I usually connect Excel files with data models in PowerBI workspaces (no only personal workspaces), and a great feature is that PowerBI service will tale care of data update, you can program the Excel data model to be updated just as you eith Power BI reports.
Nice video. However I still do not get if we are able to upload Excel files to the service and then use the file in import mode in a dataset. The reason for this is that we have some non-tech users that love Excel and need to update some tables. If they could do this directly in Excel on a spreadsheet which we just pul into a datamodel and refresh on a set scheduled that would be nice. I am able to upload Excel file to service but cannot see I can make a connection to Excel file stored in a given workspace (only on-prem files).
I manage a Power Pivot workbook that people across my company use. People like the ability to easily modify row/column labels to get the data they need. I could be incorrect, but it seems more difficult to make a "self service" data aggregation tool in Power BI. In future state, we'll probably take advantage of the Analyze in Excel option and manage it all in PBI Service.
excellent. What i've been waiting for ages for someone to explain. !. So essential. Thanks
I haven't used Power Pivot to create reports in quite a few years, but I still use it all the time to connect to Analysis Services when I need to import a cube in Power BI and so I want an MDX query. I'd love to see Power Query get the same Import Wizard one day. It's super helpful!
Great video! This is actually very useful for me, since I've been working with power pivot models on a daily basis but we want to move a lot of them to power bi. And because it's not easy to handle complex models (they are also pretty heavy, more than 500MB, some of them over 1GB) in Excel, especially when they need to be shared.
Need to mention cube functions here in how to generate the "adhoc" reporting which executives love but are hard to reproduce in Power BI!
Lots to consider. I feel like it was helpful to understand the myriad of options, but I'll have to re-watch when I want to do this.
Oh yes, I still use Power Query, Power Pivot, and DAX in Excel. I learned much about DAX in Excel years ago. There are times when I'd rather build a data model in Excel than Power BI, though most often I'm using PBi.
I had no idea you can publish Excel files to Power BI apps. This is the perfect option for departments that just want their Excel books "in Power BI" without being willing to work with you on data modeling.
Did not know about the first import method. We use the Excel Connector quite a lot in our business where we want to get constant updates from the connected Excel. No idea what’s a Power View! First time I’m hearing about it!
It looks like most of these features were deprecated in 2024 according to MS Learn, FYI to anyone confused why they can't find the buttons. You can still use Excel sources via SharePoint though.
HAs the 'get data' option ( which allows me to stay connected to my excel workbook,) gone to thru' any updates ? ( which might allow me to keep interacting with my queries and Pivot model?. i , indeed use the Power model a lot , as a simple way of linking tables and creating the relationship which allows me to produce very detail 'Pivot tables' . No need to 'through' everything out the window and star from scratch with Power Bi. Thanks Patrick !
Great video! I have exported the insane amount of data to excel and am not struggling to create metrics 😅
Yes Adam, I use power pivot and want to use an on prem xl file and publish the model and data to power bi service so I can then connect xl files to share the model online and produce lightweight xl local reports....
Great video!. Started off in Power Query + Power Pivot. Now it really depends on the application whether it's a quick analysis (mostly in excel) or publishing BI reports (PBI). However I do use PBI more exclusively when it comes to virtual tables via DAX since one can "see" the table in the data view before using it as a variable + iterator function in a measure.
Yes, I use Power Pivot sometimes. I fine it especially useful when I need calculations. The possibilities in a regular pivot table Are so limited compared to DAX.
Could you please help on how can we send Power bi reports in Excel format and through subscription how we can save it in one drive or share point location
Hi Adam, it doesn’t make sense that I can’t connect to the excel file from power bi after uploading the excel file to Power BI service. It should behave kind as a onedrive upload. The only reason I see to upload an excel file to PowerBI it to connect to it from other reports without the need to use a gateway or OneDrive which has an extra cost if you wish to have it.
@timgray7859