Power BI Insights: Power Query; Default files; Parsing with M; Audit log storage

Microsoft Power BI pros share their thoughts on removing duplicates with Power Query, opening from default files, parsing data with M and audit log storage.

Removing duplicates with Power Query

Microsoft MVP Matt Allington, writing on the Excelerator BI blog described a recent situation helping a customer that needed to load one record per customer from a table of customer sales orders. Most users would probably approach this by sorting the table both by descending order and the customer key column, but this doesn't work in Power Query. The order dates for some customers end up being different than the last orders.

Borrowing from M code steps pioneered by fellow bloggers Chris Webb and Imke Feldman, Allington turned on the formula bar, forcing Power Query to load all data into memory, which ultimately forces the correct output.

Opening Power BI with a default file

Writing on DataVeld, David Eldersveld shared a tip for using Power BI. In some cases, users need to open the same PBIT template whenever they fire up Power BI and he recommended using Microsoft Configuration Manager to manually install shortcuts for consistent startup. Users will need to copy an existing Power BI Desktop shortcut, choose properties, add the path to the target and save the entire thing.

Eldersveld shared a screenshot of the completed setup, noting that double quotation marks are only needed if there is a space in the file name.

Parsing Power Query data with M

Chris Webb recently shared an M function for making Power Query ...