How Power BI Never Will or Can Let Excel Go...
Most Self-service Power BI developers have plenty of experience with Excel. When migrating or taking the step into Power BI you may think you can let go of Excel - but don't be so optimistic.
I will show some of my best use cases when combining Excel and Power BI - and I hope you will learn a trick or two along the way.
Happy reading
When Power BI launched in 2015 - Excel already was an experienced 30 year old - which will provide some challenges in moving in a new direction, but maturity is there...
First thing with all the buzz, including from myself, that Power BI Desktop finally have dark mode. The experienced familiy member/brother/uncle in Excel did of course already have that...
How to use Excel in Power BI
I will provide some examples based on below list:
Excel connector in Power BI Desktop
The first option that Power BI Report Developers see on the blank canvas is our friend, "Import data from Excel"
... but when you browse and find an Excel-file, you actually are pointing to a local Excel-file on your pc as we can see in the M script in Power Query Advanced Editor:
Why is this a issue - it works fine when I press the Refresh in Power BI Desktop? Deceivingly, it will first become a problem when you publish the report to Power BI Service and want's to schedule semantic model refreshes - you need a gateway for these local files...
Connect to Excel OneDrive file - difficult, but easy once you know how
To solve this, we need to go down a confusing path - but once you get the habit of it you will be happy.
Excel app opens with a connection to both the OneDrive path and the local path.
3. Go into 'File' and 'Info' and select 'Copy path'
4. Go into Power BI Desktop and select the 'Web' connector and insert the long URL path
5. Go to the end of the URL and remove the ?web=1 in the string and select OK
It connect and we can see the in M Script that it uses functions of Excel and Web.Contents and the URL is not OneDrive, but actually a SharePoint site...
Publishing a new version to Power BI Service brings a new message in the Semantic model settings - as it wants to re-enter credentials.
Clicking on 'Edit credentials' and choosing OAuth2 and Organizational will now give us the option to schedule refresh of the source.
We don't need a gateway as the file resides in the cloud.
Automatic Semantic Model Updates with OneDrive and SharePoint - is that you Direct Lake?
When using OneDrive you also get the chance of setting automatic semantic model updates on - which basically updates the Power BI Semantic model with the recent changes in the Excel-file from OneDrive - WITHIN minutes. Without schedueling - that's near real-time with Excel and maybe faster than Direct Lake (just kidding)...
In this demo I couldn't find the setting for it though - I will try find it in near future.
Beware that the Fabric Admin can disable that possibility from the tenant settings in the admin portal - its named "Semantic model owners can choose to automatically update semantic models from files imported from OneDrive or SharePoint"
领英推荐
Power BI Semantic model in Excel - one version of the truth?
For me, to start with, the most important is to work and connect to the same validated semantic model - whether its in Paginated Report, Power BI, Tableau or Excel.
Clicking on 'Analyze in Excel' will open Excel with a live connection to the Semantic model
Alternatively, I could start in Excel and click 'Data' --> 'Get Data' --> 'From Power Platform' --> 'From Power BI' and then login and choose the semantic model you have developed or have build access to.
Excel workbook in Power BI - this is crazy
You can even upload an Excel workbook from OneDrive/SharePoint as a type 'Workbook' in your Power BI Service workspace or app.
From the workspace, you will get an Excel Online experience - if you have the rights, you can also edit the file from there ??
But if you don't have access to the file, you will not be able to see it nor edit it.
Power BI Dataflows Gen1 and Fabric Dataflows Gen2
I will not come with the differences between the two today, I will focus on the Power Query engine that dataflows use - but the UI is amazing and I can already say that they have solved the complicated steps to connect to Excel. Let's dive in ??
You need a Pro license you use Power BI Dataflows Gen1 and a Fabric license for Fabric Dataflows gen 2 - I will show it with a Pro license and Power BI Dataflows.
Power BI Dataflows are for reusability and that means we need a workspace other than 'My Workspace' - navigating to a workspace and add new item:
Then click on 'Add new tables'
That will bring you to a nice overview - and look in the top left corner 'Power Query' - that's familiar ??
You can simply drag your Excel-file into the canvas or Browse it or select Excel workbook connector - everything works smoothly and what you wished for. The file connection will be the local path, but the path from OneDrive/SharePoint ??????
If you use drag and drop of files, Power BI will add a new folder into your SharePoint within Documents/Apps/Microsoft Power Query
That was bananas ??????
Closing thoughts
I have shown you several ways of getting Excel and Power BI experiences back and forth. There are other options as well for example in Composite models or using the SharePoint connector that I will discuss another day.
Again, I think we will continue to see the use of both Excel and Power BI - let's get the best out of it :)
The Power Query Experience in dataflows are amazing in terms of connecting to Excel-files and want scheduled refreshes. I wished Power Query in Power BI Desktop had that. But if you are not ready for using dataflows I think this article will have given you a fair chance of solving it. Furthermore, how cool is Excel with Dark mode and 'direct lake' in Excel - are there other features we are missing in Power BI from our 30 year older brother/familiy member in Excel?
Thank you for reading my article, hope you enjoyed it. Comment or provide some input is much appreciated.
?? Hi my name is David Kofod Hanna, Senior Advisor and Consultant at twoday and my aim is to guide self-service Power BI developers for more enterprise managable concepts in a consumable and practical way - because I have been on that journey for years, and still is...
Passionate about data visualization and storytelling
6 个月Good read