Power BI Insights: Calculation groups; Confidential data; Dataflows; ADLS Gen2

February 10 2021

Azure pros share their thoughts on calculation groups, confidential data, dataflows, and ADLS Gen2. Also, in case you missed it: check out our new interview with Microsoft MVP and Power BI consultant Matt Allington? 

Controlling default format strings with Power BI calculation groups

Gilbert Quevalliers, writing on Four Moo, noted that he was recently working with customer data and discovered that a Calculation Group failed to return a Format String. Building on previous blogs about Calculation Groups, he used Tabular Editor 3, going into the Calculation Group navigating to Currency Symbol and Metric Currency.

Quevalliers copied the Format String expression into DAX studio. Plugging in the default values for the sales measure, he used double quotes to show where the DAX compiler runs. 

 "It is always important to make sure that when creating a Power BI report, that it is easy for the users to read and explore their data. Whilst this is a small fix, it is often critical when working with different currencies. It saves the users a lot of time trying to figure out if it is in the base currency or an alternate currency," he wrote.

Masking for confidential data

Gil Raviv, writing on DataChant, revisited a technique he last blogged about four years ago that involves masking confidential data in Power BI reports. Leveraging Power Query, he randomized data during refresh. The approach means users have no need to change the data on the external data source, prevents other users from reverse engineering confidential information, and masked values are able to retain dimensional identities.

Sharing the M code, Raviv wrote:

If you build Power BI apps, you can use this technique to generate real data into demo data without spending too much time on creating completely fake datasets…If you want to show the same report to both internal and external users, you can combine the masked data and the unmasked data on the same column and add a new column to differentiate between masked and unmasked values. Then apply Row Level Security to show the masked or unmasked records to relevant audiences.

Connecting Power BI dataflows using Power Query and Excel

Imke Feldmann, writing on The BIccountant, explained that currently it is not possible to connect to Power BI dataflows from Power Query in Excel. But there are workarounds that involve consume dataflows as CSV files from Blob Storage. She cautioned that when users rely on the AzureStorage.Blobs function it prevents the application of role-based security. With a data lake in-play, users can connect dataflow workspaces to Blob Storage performing a dataflow refresh.

Feldmann shared a custom function to allocate column names from model.json to the CSV file. She wrote:

With a proper setup of an own Azure Blob Storage, you can access the results of Power BI dataflows in Excel. A custom function helps to efficiently grab the data and stitches the column names and types from the model.json back to the table. Being able to consume dataflow results directly from the Azure Blob Storage opens many more use cases.

Importing data from ADLS Gen2 with Azure Synapse Analytics serverless

Chris Webb discussed how to do transformations while importing data from a folder of CSV files stored in ADLS Gen2. To begin with, he created a filter for the date column so that only a single date gets returned. With the datasets published to Power BI Service he refreshed to see how long the refresh took, and swapped out the filter for a group by operation. He wrote:

A lot more testing is needed here on different transformations and different data volumes but nevertheless I think it’s fair to say the following: if you are doing transformations while importing data into Power BI and you know query folding can take place then using Synapse Serverless as a source may perform a lot better than the native ADLSgen2 connector; however if no query folding is taking place then Synapse Serverless may perform a lot worse than the ADLSgen2 connector. Given that some steps in a Power Query query may fold while others may not, and given that it’s often the most expensive transformations (like filters and group bys) that will fold to Synapse Serverless, then more often than not Synapse Serverless will give you better performance while importing.


Photo by Jonathan Francisca on Unsplash

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About MSDW Reporter

More about MSDW Reporter