Power BI Insights: PowerQuery optimization; Data sonification; Icon names; Paginated Premium Metric Reports; Version control; Conditional formatting

Microsoft Power BI pros share their insights on PowerQuery optimizations, data sonification, new icon name option, version control and more.

Optimizing PowerQuery

Chris Webb, writing on Chris Webb's BI Blog, explored recent optimization tricks he's discovered for PowerQuery. He imagined a situation with a sizeable CSV file with numeric columns for which he would need to find the maximum value for the example. In the example, Webb used UK Land Registry data. Using the statistics-maximum option within the Transform tab of PowerQuery Editor, he quickly found a result. Because containers are limited to 256 MB, queries are often throttled.

Webb recommended eliminating all unnecessary columns from tables to reduce memory overhead, particularly for scenarios with pivots, groupings, sorts and merges. When more data is needed, users can subdivide, removing all columns except the ones they're working to transform.

Data sonification

David Eldersveld, writing on Data Veld, highlighted data sonification as a way to make reports more useful. Power BI Service has had sound capabilities for some time, but Microsoft only recently added these to Power BI Desktop. The system aligns low pitch sounds with low values and scales up to higher pitches accordingly. Eldersveld customized his example with WAV files to store sound files for subsequent import into Power BI.

This method only works well with Firefox and Google Chrome, which still autoplay audio, while other browsers disable it. Otherwise, users would need to click play individually for each column in a graph. The .25 second sine tones he imported are typical of the kind of sounds that can be played, which have to conform to the 32,000 character limit in Power BI text fields.

Icon names in Power BI

Microsoft MVP, Matt Allington, writing on the Excelerator BI blog touched on new icon features released in July. Icons can be referenced in a measure by name. He shared an example of code that returns SVG and standard icons for certain number ranges, as well as animated gifs. Reaching out to other bloggers, he learned that there are 67 standard icons and he put together an interactive graphic showing the full list and what they look like.

Unfortunately, SVG images weren't working due to a code issue when Microsoft first issued their workbook on PowerBI.com, in part because many browsers struggle to interpret the hash icon accompanying SVG files.

Paginated Premium Metric Reports

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us here

Get full access

or login