1. Transforming and formatting columns
Let’s continue cleaning our data. In the table view, we can load the Power Query Editor by clicking Transform data. Let’s look at DimProducts.
If we select a column, there are a couple of things to note. For example, for Standard Cost, we see that the data type is a decimal number. This makes sense here, but note there are other options, like percentages, or for something like Color, we have text.
We see there’s something wrong with the Subcategory column. It has a question mark in front of every value, which is probably a processing error. To fix it, we select the column and then use replace values to search for all the question marks and replace them with blanks.
Let’s close and apply to save our changes. We have seen how to set data types for columns, but what about formatting? For example, say we want to have the standard cost always appear as a currency in the visual we create.
We can do that in the Table View. First, we select the Standard Cost column and column tools appears above. We can change the format from general to currency. We can also change the number of decimal places by adjusting this section. Let's set it to zero.
Another useful feature is the Summarization field here. You can change the default summarization type for a specific column from here , so that you don't need to keep changing it each time.
To see if our changes have been implemented, let's add a card visual to the report, and display the standard cost. Great, we see the average value, with currency format applied and zero decimal places.
Let’s look at geographic data. In the DimCustomers table, we have several columns representing geographic areas. We can put these in a data category, like City or Country. Data categories allow us to specify what type of geographic data the column contains.
Using these categories, we can create map visualizations. Note that globe icons have shown up next to the City and Country-Region fields, indicating that they are recognized as geographic values by Power BI.
Let's add a map visual by clicking on the corresponding icon. We can then use the Country-Region as the location and count the number of orders placed by country using the SalesOrderLineKey as the bubble size. Now, the circles on the map are proportional to the quantity of orders placed in each location.
Time for you to give it a go!
2. Let's practice!