Power BI Insights: Data privacy partitions; SharePoint Online; Power Automate exports; Power Query

Power BI pros from the Microsoft ecosystem share their tips on data privacy partitions with Power Query, hurdles of using SharePoint Online, Power Automate exports and doing API calls.

Seeing data privacy partitions with Power Query

Chris Webb took a look at Power Query Query Diagnostics, a feature added to the Power BI line-up back in May. It offers users the ability to see data privacy partition information, and Webb took it for a spin. With a simple query, he read DayName data from an Excel file, returning DayName data in its output.

With a second query, he pulled names of days of the week from SQL Server database and an Excel workbook and was able to determine that both datasets had their security level set to Public. With the Diagnose Step, he generated a three row diagnostics query displaying the security settings. For users, the feature provides important insights, particularly because data passing from one partition to another it may get buffered in memory, slowing down performance.

The challenges of Power BI and SharePoint together

Microsoft MVP Matt Allington, writing on the Excelerator BI blog, reflected on some recent experiences working with SharePoint Online as a data source for Power BI. “On the face of it, it seems like a great idea to leverage SharePoint as a storage location for CSV and Excel files,” he wrote. That’s because team members can easily access and share files, with SharePoint managing version control and shared file editing. But sadly, Power BI performance suffers in the process.

According to Allington, SharePoint’s OData API is part of the problem, driving down refresh times. But the list of challenges grows, with slow query editing when there are multiple SharePoint connections in a PBIX file. For a workaround, he adopted the approach of syncing local files to up performance.

Exporting paginated reports with Power Automate