Thursday, June 23, 2016

Power BI - Replacing Values using the Query Editor

Data sets can have data problems. Often it's an unknown value populated with filler. For this example, we imported data from our Google Analytics and found that our Country Column had unknown values populated with the phrase: (not set). For our report, Unknown would be a better.

Using Power BI Desktop, changing (not set) to Unknown is a simple task.

Detailed

From the Home tab, first select the table (Country for this example) and then click on the Edit Queries button:



This opens the query editor. Our objective is to replace the value (not set) with the value Unknown.




Replace Value Pop-up

You have two approaches to starting the Replace Values pop-up window. For both, first highlight the offending value - notice that the first row with the value (not set) is highlighted (dark gray).  

You can also select the entire column. But, if you select a specific value in a row, the Replace Value pop-up is pre-populated with the offending value.  Useful if the value has any special characters.

Once you have highlighted the value, either select the Replace Values button, or right click on your mouse and then select Replace Values.



Both approaches open the Replace Values pop-up.



Type in the new value - here it was 'Unknown.' And then click on the 'OK' button.



The values are now updated.  

Changes and Adjustments

If you need to make a change to the new value, simply click on the 'Gear' to the right of the Applied Steps transformation labeled Replaced Values.  




This brings up the Replace Values pop-up, pre-populated with the original value (not set) and the original Replacement value.  Just alter the 'Replace With' value, and click on 'OK'.


No comments: