Data preparation – Data Azure

How to connect Power BI Dataflows to Excel

December 30, 2021December 30, 2021 by dattrieuds, posted in Dataflows

Power BI dataflows (PBI dataflows) is a powerful data prep tool for you to transform data and reuse them in other places downstream. At the same time, while Excel is very popular among business users, there is still, at the time of writing this post, no out-of-the-box connector to import PBI dataflows to Excel. This post aims to solve that pain point.

This post also proposes an alternative to the existing PBI dataflows connector to Power BI Desktop to push the users’ access control to the table level, instead of workspace level like the current connector.

Continue reading →

The Excel Connectors in Power BI

November 28, 2021 by dattrieuds, posted in Power Query

Like it or not, Excel is one of the most popular data sources to build Power BI reports. Surprisingly, except for when you want to connect to an Excel file that is located on your local laptop or a shared network drive, there is no easy way for users to connect to an Excel file that is hosted on cloud services via the Power BI’s user interface (UI).

This blog post will provide you with a universal M script, which you can use to connect to Excel files hosted on not only on-prem sources but also cloud environments, such as OneDrive for Business, SharePoint, and Google Sheets (both public and private). You can download the pbit file in my GitHub repository for a quick start.

Continue reading →

Power BI dataflows to store historical data

October 25, 2021October 27, 2021 by dattrieuds, posted in Data preparation

Power BI dataflows is a nice capability of Power BI. It’s actually not a new Microsoft product. If you use Excel and Power BI long enough, there is a big chance that you’ve already used Power Query at least once. And basically, to keep things simple, it’s just Power Query, but you have to edit it on a web-browser, in Power BI Service.

Pro license is your entrance ticket to use this nice capability. Although Premium licenses (per capacity or per user) provide more advanced features, many applications of Power BI dataflows are already available with Pro license. One of them is to store historical data, and of course, to show them in reports.

In this blog post, I will briefly introduce Power BI dataflows (with some useful links if you want to learn more). Next, I’ll share how to store historical data in your own storage account and show both current and historical data in a Power BI Report (with ready M scripts, and a pbit file posted on my GitHub repository), so you can roll up your sleeves and start right away with your own use case!

Continue reading →