Different ways to work with Microsoft Excel in Power BI (2023)
HTML-код
- Опубликовано: 22 фев 2023
- Using Excel? Of course, you are! How can you take advantage of Power BI? Adam shows you the different ways you can leverage Excel with Power BI to get the most out of it!
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Excel #GuyInACube 
Наука
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.
excellent. What i've been waiting for ages for someone to explain. !. So essential. Thanks
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!. 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 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!
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.
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).
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!
Need to mention cube functions here in how to generate the "adhoc" reporting which executives love but are hard to reproduce in Power BI!
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.
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 !
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 😅
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 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
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?
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?
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.
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
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…
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
Hey man, is it possible to update data in a powerbi report, while in the report, without power apps?
Power View! Yes. I have heard about it. Did I just win a prize for that? XD
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
What will performed better csv, xml or xlsb?
Coulf you please show in some video how to transform Planner data to Power BI?
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.
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
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.
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 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.
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.
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
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
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
A wrap up is missing
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.
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
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."
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.