Bring Power BI and Excel together

STOP defaulting to exporting your Power BI data to Excel! Adam shows you options you can use within Power BI to still work with your data in Excel if that's what you are comfortable with.


*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.


*******************
LET'S CONNECT!
*******************


***Gear***

#PowerBI #Excel #GuyInACube

[utubeAgape]

I always audit accuracy of a PBI by exporting the data to excel because there could be errors in the measures and/or the model - pivot table analysis from data export to excel is like auditing formulas in an excel spreadsheet. A must!

[NormRidg]

I work in manufacturing and we analysis data constantly. A statistical package PBI is not. You could learn Python and R, but most users have packages like MatLab or Minitab. Also, the Excel pivot this was neat till we realized only measures could be used as values. Bummer.

[juan_urbina]

Lack of free form input is what keeps me in excel. Example, I need users to be able to enter their sales commitments by product, for the month and quarter, and their input is measured against their area only.

[alfredlear]

Okay, I'm using this tomorrow. Most of my coworkers export to excel whenever possible.
Pivot tables confuse many too.
People believe the data, but mostly want the conclusions wrapped up for them.

[gonzalorengifo]

I only export data to Excel when I want to share certain information outside the company, for example, with suppliers. Also for specific analysis of a store or supplier I go to the Excel Power Pivot (that is connected to the same dataset as Power BI dashboard) and copy the information from there

[johnnyw]

Nice. There are loads of other things you can explore on this topic too. Maybe you can get Chris Webb on to talk about Cube Formulas, which are cool addition to PBI's Excel tool belt. You also have the possibility of featured tables. Wyn Hopkins did a really good video recently about creating refreshable linked Excel tables, and if you have an XMLA end point you can use DAX Studio to create linked Excel tables easily too. I used to think Excel was the bane of my life, but I've learned to embrace it - giving business users the choice of how they interact with your datasets is a really powerful thing.

[davejl]

Analyze in excel don't allow you to group / cluster (sum, average, etc) those numerical fields in the original columns. You have to have measures defined explicitly to summarize the information. This can also be simulated using dax studio.

[erickelley]

Snapshotting datasets & freeform entry & dataset integrity concerns are scenarios where I consider solutions which involve Excel. Maybe a team doesn't really have their whole project process thought through and they want to figure it out as they go. I tend to step back from that & Excel works well for them. Well thought out process generally translates well to a PowerApp front end and a more stable back end than Excel. There are so many learning barriers in that handful of sentences & often the best care is to just let'm have at it in Excel.

[collontomlyn]

I got my team using AiE and it has been a total game changer--no more shared drive files, no more emailing workbooks, and when an update needs to be made it is super streamlined across all users. HIGHLY recommended to use if you're in a data analysis role.

[brijendraydv]

Biggest reason is no write back capability in PBI, users want to enter manual inputs and comments on the downloaded data.

[Wardog]

I usually export due to my administration wanting a filtered data set on a spreadsheet. They are not comfortable or (in my opinion) wanting to learn Power BI. That and they want a bar/pie/trendline chart to specific data that they can see without drilling down in Power BI.

[NickM]

One way I have dealt with this is to grab the actual DAX query from the Performance Analyzer and paste into DAX Studio. Then from there I can make some small changes. Then I run that query and generally paste the results into an excel file. Then just a little formatting to make it look pretty and good to go. As an added bonus I always put the actual query into a hidden tab as a reference if it ever has to be updated. Not the most efficient method for sure but solves the issue of getting the data into Excel without giving the end user too much power to make changes.

[juanpablorvvv]

Analyze in Excel gives the users access to the whole model and they would have to know how the tables are related and the types of relationships used etc. but they only wanted the small table that was in the report, not the whole data model. I think analyze in excel is only useful for the report designer, not for the final users that don’t even know what’s a relational database. Also, analyze in excel uses the original column names that are meaningless to final users, they want the names in the tables in the report, not the model names.

[ahmetyildirim]

I think we always want to be sure that the numbers are correct. The easiest way is excel

[sajjad]

We face huge latency when filtering data in analyze in excel option, so looking for other ways to get data using VBA.

[tonylather]

My boss likes to export a table from my Power BI report, filtered or not filtered, sometimes one table, sometimes more...however, he asked me to provide an easier method to do so. I created a Power Automate button that looks like it should work, but it appears that it wants to save the file in either SharePoint or One Drive and I do not have direct connectivity to those.

Is there a way to export to a new Excel file, and just open it in Excel on the user's desktop? That would be optimal.

Thanks

[project]

I love to bring the featured tables of dataset via Excel datatypes! This is a simple stupid way to bring data into Excel :)

[awarrentfa]

If someone were to use "Analyze in Excel" and add/change data (primarily by filling in columns or adding new rows), is there a way that Power BI could take these additions and bring them back into the report itself?

[udusegbe]

I authored a PBI report and shared via Apps and still users want to export to Excel! I guess reasons provided below are all valid, it seems. By the way, one can't export to Excel while viewing in Apps right? I haven't figured that one out

[younissyed]

Dear Guy in a cube team,
I have prepared a report now my client give me a excel file which has several columns and data in it and now based on this excel data has to be excluded from this report.
Thank you in advance