Power Query / Power Pivot within Excel used a lot in some Finance and Accounting roles. Definitely some advantages for recurring month-end processes that don't need visualizations, just values for booking journal entries. Also great for Ad Hoc / one-time use type files.
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.
As always, great video. I have an excel worksheet that lives on SharePoint. It contains a reporting structure with filters that we can easily update on the fly. My question is from a performance and transformation point of view when pulling this into a dataflow, is it faster to keep in SharePoint, or upload directly to a workspace?
We were so glad when Power View was replaced by Power BI Designer, and even more excited when that was replaced by Power BI Desktop! We stopped using Power View as soon as Designer came out. Never missed it once. Absolutely we have a ton of Power Query in Excel-more than we have in Power BI Desktop. We probably even have more Excel Power Pivot than Power BI (Desktop or Service) in accounting, but the rest of the company probably doesn’t even know about Power Pivot. I appreciate the flexibility in moving data from one tool to another. The missing one is from SSAS to Excel or Power BI. We have a manual process using DMVs, VBA, and Tabular Editor and hope to be fully off SSAS someday soon. The XMLA endpoint option was great when it worked but hasn’t for years now.
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!
I frequently work with files larger than one million rows, so the Power Pivot data model is the only way to generate findings in Excel. That, combined with the ability to use DAX measures and Power Query, makes it a great option.
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.
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 !
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!
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, We can show the percentage of grand total in only pie and donut chart can we see these items for other visuals as well. Please let me know if I am missing
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....
Im looking for a way to use my Power BI dataset in Excel, similar like Analyse in Excel. Except, instead I dont want to use a pivot table, but a normal table. All fine thus far but what I couldnt accomplish is to the add a Parameter to filter the data. So user can (for instance) input which item they would like to filter by. I don't think this is possible at the moment.
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.
Haha definitely I'm a Power Query and Power Pivot user from Excel. Also had the opportunity to use Power View. For some reason Power BI is not easy to be found in companies 😅
Great video! Makes me curious… would it be possible to 1) have an excel workbook in the workspace that my customers could access and 2) allow them to update via the app for that workspace and that 3) then gets used as a dataset for a report in that same workspace and app? User could change data and the see those changes affect the report…? Like if the want a custom Dim that they can modify on the fly…
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.
Q: great video! I’m still new to power bi and this is all still very confusing. We have excel file in our sharepoint online and a shared workspace for my team. We update the excel file with different data once a month and want to then update our reports and visualizations in power bi to show / add the new data. It might be different people doing this each month. What’s the best way of doing this? Also is there a way to keep the visualization as is without having to redo everything every month?
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.
Question: once the workbook is loaded to an app how do we schedule a refresh. The file has to be saved locally to be uploaded so i’m thinking VBA “refresh all connections upon opening” then a desktop flow to open the files. Then once in the app i’m unsure besides manually refreshing connection and updating app
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.
@@HachiAdachi question: once the workbook is loaded to an app how do we schedule a refresh. The file has to be saved locally to be uploaded so i’m thinking VBA “refresh all connections upon opening” then a desktop flow to open the files. Then once in the app i’m unsure besides manually refreshing connection and updating app
@@04mdsimps if the file is saved on OneDrive for Business or SharePoint, you can simply schedule a refresh as you would with a Power BI dataset. The Excel file needs to have a data model (Power Pivot) for this to work. If the file loads the data directly from PQ to a worksheet, Power BI doesn't even give you the option to schedule a refresh.
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.
Q: What is Power View sheets? Also, I’m importing excel files as datasets, but this files are macro enabled type.. Q: does it make a difference if is normal excel file or macro enabled file?vv Thank you guys. Your videos are the best!
I still use Power Pivot in Excel, comes very handy to write DAX measures inside an Excel file, most people just drag and drop implicit measures there and I cringe when I see that. Most people don't even know you can write DAX in Excel.
Great video. But I have an urgent question that I cannot seem to find the answer to. When I do the Analyze in Excel against a Power BI Semantic Model (Fabric or Regular Power BI), it does create a nice Pivot Table from my Semantic Model. However, Excel does not seem to recognize the relationships that are in the model. So it makes it useless. For example, my client is a property manager. So a typical thing they woudl do is put the Property as a Filter and then select one or more properties and want to see the related Leases for the Properties. But when they select a Property, it does not filter the list of Leases that are placed in the Rows. I convinced them to go to Fabric away from SSAS, but if I cannot get this to work, we are stuck. They have a ton of Excel models that need to use relationships. Please help if you can. If anyone can figure this out, it is you guys! Thanks
I've sometimes replicated a model I've built in power bi as a power pivot model in excel (data source sql server) because excel connected to power bi treats dates as text, and lord help you if its in it's default long date formatting.
I have a requirement where I have to get values from 17 different worksheets of a excel file, so I wrote a python script that goes to each sheet and gets this single value and creates a dataframe. Now I am using this script in Power BI to get the dataframe into my model. But when I am trying to schedule a refresh it says the datasource is not supported. Is there any workaround or solution to this? Please help me out.
once again thanks for great video. Is kind of weird though that Microsoft does not make the 'interpahse to Power BI' EASIER to use, as you rightly said, MOST people work with Excel around the entire WORLD !. rgds martin
What is workspace? Power BI is a dashboard business intelligence tool:application. It displas the data and tables. Can we extract the data from the dashboard?
Hi Adam, I wonder if below scenario can be achieved: I have an Excel file with PowerPivot backend tables with data coming from PowerBI dataflows. This Excel is distributed by e-mail on daily basis. The pivot tables inside should be refreshed and up to date. Can this refresh be automated without opening the Excel file? i currently have to open the file each time, refresh & only then distribute by e-mail.
I second this. Set up a flow in Power Automate to trigger a refresh of the file when the dataflow refresh is complete, then finally have the file emailed out automatically. It works well for me!
@@HachiAdachi thanks for letting me know! What are the names of the steps to refresh a pivot in an excel online-file without having to manually open it? Another important note is that the computer could be turned-off at time of refresh. Thanks again!
@@kiterbram You would use "When the dataflow refresh completes" trigger and the "Refresh a dataset (Power BI)" action in Power Automate. Your Excel file should show up in a dropdown among other Power BI datasets. Power BI will need the file to be saved in OneDrive or SharePoint to be able to refresh Excel data model (therefore your computer can be turned off).
I have scheduled refreshes of Excel data by using ‘get data’, ‘Other’ then ‘Web’ then select path. I sometimes have to clean the file path, but it works fine
Upload from workspace doesn't work "Power BI no longer supports the upload of Excel files from local folders. To upload or view your Excel file in a Power BI workspace, re-save your file in OneDrive or SharePoint."
@@sallyho146 basically I mean that I would like to share a report to 5 people outside our organization by adding my customers email to invite them to view the report without sharing publicly. Click on report. Invite users to read. Without anyone else being able to view the report.
I like your videos, but it´s not easy to follow you. Why? I´m not a native speaker and due to your speed of speaking it´s really hard to watch and to understand at the same time.
