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.

excellent. What i've been waiting for ages for someone to explain. !. So essential. Thanks

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.

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.

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!. 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.

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!

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).

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.

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. I use power Pivot data model, moving to Power BI soon. Either importing existing data model or start fresh with BI.

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....

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 !

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! 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…

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

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 😅

Oh nice. Editable Excel file source, in the workspace, may solve some simple reporting problems

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 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

What will performed better csv, xml or xlsb?

Hey man, is it possible to update data in a powerbi report, while in the report, without power apps?

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.

Coulf you please show in some video how to transform Planner data to Power BI?

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

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.

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.

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!

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

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.

@04mdsimps

Жыл бұрын

Exactly my thoughts. When was this feature introduced?

@HachiAdachi

Жыл бұрын

​@@04mdsimps it's been around for a while... I think I've had Excel files in Power BI Service at least since 2019...

@04mdsimps

Жыл бұрын

@@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

@HachiAdachi

Жыл бұрын

@@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.

@04mdsimps

Жыл бұрын

@@HachiAdachi i see. Will have another attempt today. Been many years since i’ve used power pivot

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.

data analist relation with sites

PowerBi will be much more popular if reports can be shared externally with security. No public sharing

Power View! Yes. I have heard about it. Did I just win a prize for that? XD

Yoo! This is Arya How do you edit the dataset after importing Excel model?

@martinpaulbannister1745

Жыл бұрын

With Power Bi open, you can edit data with transform data. Power query opens, so u can clean, merge, append etc

Yup i used power view addin for xl and ppt

@HachiAdachi

Жыл бұрын

You can still get Power View Add-in? Thought it was gone for good! 😮

@seanmacdonald4948

Жыл бұрын

@@HachiAdachi no but in the vid he asked comment if you know what PV was and i did lol

@HachiAdachi

Жыл бұрын

@@seanmacdonald4948 oh, I see. Understood... And a bit disappointed... Lol

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.

@jensvonkracht6770

Жыл бұрын

Fist thing coming into my mind was Power Automate. Have you tried that option already?

@HachiAdachi

Жыл бұрын

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!

@kiterbram

Жыл бұрын

@@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!

@HachiAdachi

Жыл бұрын

@@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).

@garrybye4415

Жыл бұрын

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

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.