Power BI Insights: PBIX memory; Pivoting text data; Time problems; App navigation; Removing HTML tags

June 12 2019

This week, Power BI pros share their perspectives on PBIX memory consumption, pivoting text data, resolving time problems and more.

The question of PBIX memory consumption

Gilbert Quevauvilliers, writing on the FourMoo blog, reminded Power BI users accustomed to PBIX files being smaller than 1 GB that in Power BI Premium, they play an unexpected role in how much memory a data model will use. Memory use depends on five different factors, including PBIX file size, columns hierarchy, user hierarchy, relationship and dictionary size. When Power BI Desktop opens, four of the five components are expanded into memory.

Quevauvilliers gave the example of a 55.9 MB PBIX file that ends up using 475 MB, made up largely of the dictionary sizes of internal tables. In addition to sharing a chart for estimating data use, he recommended doing incremental refreshes for individual partitions, thus using less memory.

Power Query to pivot text data

Microsoft MVP, Matt Allington, blogging on the Excelerator BI blog, delved into the need to transform data before use in Power BI. Often older stems output csv data extracts that don't align with goals in Power BI and need to be rearranged. Simply clicking the "Pivot Column" option in Power Query isn't enough, because Power Query can't record unique record sets.

Allington shared a sample workbook, in which he created a new numeric index column to uniquely identify each record. Back under the Pivot Column's advanced options, he set the program not to aggregate and wiped out the index column.

Resolving Power BI time problems

Also on the Excelerator BI blog, Allington shared an experience working with a customer in Sydney that needed to do reporting in a rolling time window. He built on the theme, imagining an even shorter time frame of just a few hours with his own solar electricity data. Power BI compresses data making unnecessarily precise time readings a hindrance. Therefore, it may be best to split dates and times into separate columns. Allington shared a sample workbook and a video of how he dealt with data not loading correctly from previous days.

Improving Power BI app navigation

About MSDW Reporter

More about MSDW Reporter