Excel Data Cleansing

Cedric Charlier continues his series on fixing up an Excel file.  First up is turning results into an enumeration:

We’ve previously decided that a DON’T KNOW, shouldn’t influence our average of the answers. To apply this decision, we just need to filter the table Result and remove all the values equal to 0 (Enum value of DON’T KNOW). Then we calculate the average and subtract 1 to get a value between 0 and 4. Coooool, except that if we’ve no value non equal to 0, it will return -1 … not what we’re expecting. We’ll need to validate that the average is not null before subtracting 1.

The next post involves converting respondent information into a dimension:

In this table, only the results with a QuestionId equal to 111 really interest me for a merge with the existing table Respondent. If you’re familiar with the UI of Power BI Desktop then you’ll probably think to create a new table referencing this one then filter on QuestionId equals 111and finally merge. It’ll work but applying this strategy could result in many “temporary” tables. A lot of these small tables used only for a few steps before merging with other tables tend to be a nightmare for maintenance. You can use the “Advanced formula editor” to not display this kind of temporary tables and embed them in your main table.

Read on for more.

Related Posts

Diagramming Databases With Power BI

Philip Seamark shows how to visualize the relationships between tables using Power BI: The network navigator was another good visual, and if you have an R instance installed on your local machine, you can play with some of the custom R visuals. The catalog views could be used in a similar way to generate power […]

Read More

Web Scraping With Power BI

Imke Feldmann shows how to use Power BI to scrape multiple tables from a webpage: I will present 2 methods here: Append-method: This is the obvious one and is fast for just a few tables. Add-Column-method: A bit more complicated but will be faster for a large number of tables and is also suitable for […]

Read More


February 2017
« Jan Mar »