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.
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 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.
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?
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.
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 !
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.
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!
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.
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
I have a dataset that will be updated every month with student behavioral metrics... is there a way to link a live Excel file from Sharepoint to Power BI? Or should I just import from OneDrive and keep updating that local file?
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, 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
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.
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…
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?
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....
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
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: 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!
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.
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.
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.
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.
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
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
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 😅
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?
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
@@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.
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."
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.
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.
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 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.
Exactly my thoughts. When was this feature introduced?
@@04mdsimps it's been around for a while... I think I've had Excel files in Power BI Service at least since 2019...
@@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.
@@HachiAdachi i see. Will have another attempt today. Been many years since i’ve used power pivot
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?
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.
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 !
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.
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!
excellent. What i've been waiting for ages for someone to explain. !. So essential. Thanks
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.
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
I have a dataset that will be updated every month with student behavioral metrics... is there a way to link a live Excel file from Sharepoint to Power BI? Or should I just import from OneDrive and keep updating that local file?
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,
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
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.
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.
Great video! I have exported the insane amount of data to excel and am not struggling to create metrics 😅
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…
Hey man, is it possible to update data in a powerbi report, while in the report, without power apps?
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?
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....
I love PQ, but I don't know how to use data models. I've never used Power Pivot, either. Should I?
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
Hi. I use power Pivot data model, moving to Power BI soon. Either importing existing data model or start fresh with BI.
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: 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!
What will performed better csv, xml or xlsb?
Coulf you please show in some video how to transform Planner data to Power BI?
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.
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.
Do you know how many formula dax has? I recently wrote Dax formula for text instead of value in the pivot table summation field.
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.
I connect to my Excel files in SPOL with the Web connector. Get the Excel path from Info, delete the '?web = 1' part, and I'm connected to the file.
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.
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.
Fist thing coming into my mind was Power Automate. Have you tried that option already?
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
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
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 😅
Yoo! This is Arya
How do you edit the dataset after importing Excel model?
With Power Bi open, you can edit data with transform data. Power query opens, so u can clean, merge, append etc
Oh nice. Editable Excel file source, in the workspace, may solve some simple reporting problems
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?
Yup i used power view addin for xl and ppt
You can still get Power View Add-in? Thought it was gone for good! 😮
@@HachiAdachi no but in the vid he asked comment if you know what PV was and i did lol
@@seanmacdonald4948 oh, I see. Understood... And a bit disappointed... Lol
Power View! Yes. I have heard about it. Did I just win a prize for that? XD
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
data analist relation with sites
A wrap up is missing
I am still not sure how to use power Bi or if I should or can.
PowerBi will be much more popular if reports can be shared externally with security. No public sharing
So are you saying that no public sharing for power BI dashboard to 1 sales consultant the company has?
@@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.
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."
HOLD UP we can include excel spreadsheets in power bi apps?! I was completely unaware of this
Can we just have excel as a visual in PowerBI. This has got to be default and free
No experience with powerview
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.
No and no
God what a garbage application