DAX is hard. Excel formulas are much easier for people to understand, since you can work with 1 cell at a time, and get visual feedback through color coded ranges. Excel also lets you enter values, formulas, comments, with a single click. People aren't willing to give that up and shouldn't have to. When people analyze data, they want to do more than filter, expand, and collapse, they want the ability to do calculations on the fly. PBI has a lot of unique features at well, such that neither can be a complete solution today for analysis for everyone. Appreciate the video! Regardless of what features we wish existed, it is always helpful to know the tools at our disposal.

The biggest reason I see for people needing to export the data is that they want to create their own formulas against it. People are very familiar with XL formulas, and they work in a much more intuitive way than Dax. Dax is a complicated language, as evidenced by the 700 page tome written by the Italians, which is necessary to fully read twice, if you really want to understand it.

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!

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.

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

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.

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.

I export to maintain backups of a weekly report. I go to the table view and copy table and paste in excel works for ~1M rows. I also export to provide detail/row level reporting to management and vendors for various accounting and operations related functions.

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

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.

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

Thanks for sharing ❤

The other cool thing about Excel > Get Data is that Promoted Dataset will appear at the top of the list. Really helps on enterprise settings to keep users using the curated/certified datasets.

The ability to go Data->Get Data into the PBI dataset from within Excel is no joke the keystone of the whole PBI ecosystem. Users can drill the pivot tables into transaction-level detail too. One trick though is that the joined fields from the data model are omitted from the drilled views for some reason. So, for example, you need to create a duplicate date field in your fact table for the drill to include it, if you’ve joined the original date field to a date table.

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.

My experience is that people use the data not only looking at reports but mainly for daily work. Here the well known excel options to look at each row and column is a must. Therefore I have trouble keeping people out of excel. If you could do a video on how to look at very detailed data with filters (like Pivot in Excel) it would be great. Thanks for the videos!

OMG we have been asking for dataflow support in Excel for so long, this is a game changer for us (can't believe I missed it, when did it arrive?)

A datafllow that filters data at the row level (RLS) based on the user is my dream feature. It would solve so many of our data access scenarios.

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.

Thanks! I think people want to have it in excel to make some calculations which are not represented in PBI Report..

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.

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.

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.

I rarely export data, usually when the information I need is on a fixed date range and I will save it as an isolated report. Otherwise, I will always connect to the dataset and get the variable and incremental data.

I distribute new sales revenue projections evenly over a period of time based on contract durations. CRM only gives me the total amount of hundreds of opportunities and power bi let's me distribute that estimated revenue recognition over months. To compare changes in this revenue projection I export a snapshot of that distributed sales once a month. That is my only use case when I export data. When I have a table that is created with power query transformation that I need to compare over time

"why export data to excel?" my colleagues like adding lookups (eg plan data), creating charts, then creating ppt slides... change management is like a concrete wall

That’s the story of my life: every one export to excel and power bi is left behind by consumers. Definitively the main reason is because they can’t input data nor format it. For example, many of the times, they export it to excel just so they can place a check mark per row when the task is done and so they can have their personal control of what’s their progress.

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

copy and paste to excel would help us check formulas, and do some simple analysis that were not done in PBI. Ex: a % growth, or of the total. sure it can be done in pbi but many times it is not planned.

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

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

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?

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

Any way to link a PowerBI table to Access?

Can I export the millions of rows from Power BI desktop now, when can we see that option? Thanks in advance

Can this be done but in a Google Sheet? Didn't see the POWERBI Option when looking at the Data Tab.. I would think there has to be a way, right?

No mention of querying (with dax) the power bi dataset to download the data directly into excel? 😭 I guess it's not really usable by the end user 🤔 but it's been a solution I've successfully established for endusers

Analyze in Excel seems to be great feature, but when trying it, I noticed that dates are formatted as text => sorting and filtering doesn't work as intended. We partly solved it by adding a column with numbers (1 Jan, 1900 = 1, etc), and used this to filter the pivot table in Excel. It's not elegant, so if anybody has a better way of working with filtering and sorting date-fields, I'm all ears.

This feature is amazing but I often face one critical problem in my org. So I followed all the best practices of data prep and model optimization like having data processed at as upstream as possible, removing high cardinality fields and other fields if they not used in power bi, and finally grouping the data. Thing is user when arrive at some point after selecting few things here and there they now want to see detailed data like at transaction level. But since I didn't call many fields in power bi model, I think of having a direct query table and implement agg+direct query model. But again there are limitations in this like iterator functions not allowed and many more which are currently used in calculated measures. And now I am always stuck. How should I exactly provide this detailed data to users

@anyone When Analyzing in Excel, why is it that numbers added to the rows of the pivot table cannot be sorted largest to smallest, or smallest to largest? Analyze in Excel sees the numbers as text rather than numbers. For the same pivot table created from scratch inside of Excel, numbers added to the rows are are seen as numerical values. I'm trying to understand why MS did this?

From what I have seen, Microsoft gives you three different options to do a task. With this video, it looks like getting data from PowerBI to Excel keeps with Microsoft’s practice of providing multiple options.

Here is my problem. I have awesome dashboards in PowerBi but I need users to provide update comments on specific line items in excel. The comments need to stay connected to each line item after every refresh from the PowerBI data set. Is that even possible automatically in the GET DATA from PowerBi option or do I need to manually export and have power query in excel get that data in as the first source and the second source being the comment column in that table itself?

sadly not for Mac...ms needs to catch up on macOS pbi

I miss perspectives on Analyze in Excel. I use a lot of measures and fields that do not make sense for AinE, but are crucial for visualizations. Hiding them in smart way would be great

Is every dude who became a "data analyst" a bald dude with square glasses and a thin graying beard?

*chuckle* Is it possible to export from Power BI to an Access database? And wait for it... Wait...for it.. Can I run VBA macros over a Power BI table?

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

👏🏾👏🏾👏🏾

Why am I not seeing "From Power BI" in my Get Data dropdown list?

Users export their data to Excel from Power BI probably for the same reasons why they bypass Power Query in Power BI. They were never taught the best practices.

Is that pro?