Filling empty rows 129

You have different options on how to aggregate data when doing the pivot transformation, as follows:

Figure 4.36 – Pivot options

For values that can't be aggregated, there is also a Don't Aggregate option.

In general, you can test the unpivot and pivot features and see what logic best suits your data cleaning needs.

Filling empty rows

Data sources can have their own specific structure on how data is shown. This may end up with having null values displayed on the table once this is imported into Power Query. In the following section, we will see an example that shows how you can fill missing data by keeping the original data source logic.

Getting ready

In this recipe, you need to download the following file in a local folder:

• FactInternetSales2 CSV file

In this example, we will refer to the C:\Data folder.

130 Reshaping Your Data

How to do it

Once you open your Power BI Desktop application, you are ready to perform the following steps:

1.Click on Get Data and select the Text/CSV connector.

2.Browse to your local folder where you downloaded the FactInternetSales2 CSV file and open it. A window with a preview of the data will pop up; click on

Transform Data.

3.You can see how at OrderDate column level, there are some missing values:

Figure 4.37 – Missing values

4.Select the OrderDate column, browse to the Transform tab, click on Fill, and then on Down:

Filling empty rows 131

Figure 4.38 – Fill down option

5.You can see how there are no more null values, and they were filled according to existing dates:

Figure 4.39 – Filled values

The alternative is to fill up, if the logic behind your data requires it. In this way, you can correct your datasets in order to prepare yourself for the next use.