Bring Power BI and Excel together
HTML-код
- Опубликовано: 1 авг 2024
- 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.
📢 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 
Наука
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 initially had that same mindset but DAX has a lot of similarities. This is what helped me grasp DAX a little better. When using DAX your referring to rows and columns basically referring to tables in excel your referring to cells for the creation of formulas.
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.
You can achieve that with field parameters in powerbi
Create an excel per person that pulls in the base data from a dataflow and then pull their excels into powerbi. Better yet if you have the skills in ur company create a powerapp that creates a little interface for them to do it and then pull that data via metaverse into powerbi
If you enter the data in the sane columns every time, say from/start date, to/end date, commission rate, sales commitment, sales agent, area you can save excel file with an agreed name that the developer uses to update/ calculate what you require. Alternatively if developer is etl developer, they can do etl to upload to datawarehouse.
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.
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!
Agreed, sometimes it's just not easy to trust the accuracy of a measure/calculation created in DAX.
Very true, though dax Studio also helps on that front
@@FrostySnowJr I’d like to get DAX Studio👍
Biggest reason is no write back capability in PBI,users want to enter manual inputs and comments on the downloaded data.
You can build a Power App that writes back to the data source and embed the app into your Power BI Report.
@@dhruvinshah4817 i think there's also even power automate capability by now (which helps stay within the known powerbi verse). Though I'm not too sure since I've not had the time to actually work with it myself)
@@dhruvinshah4817 thanks but still not useful as you don't want to build app for simple commenting and sharing to others,it's pure business users case.your leadership team never wants apps😁
Exactly this!
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.
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 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
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.
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.
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.
Thanks for sharing ❤
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.
Thanks! I think people want to have it in excel to make some calculations which are not represented in PBI Report..
They should just ask the report designers to add those calculations because other people might be wasting time exporting and creating the exact same formulas and there’s a greater chance of human error at that point.
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.
You can actually just paste the dax query itself into excels version of power query. I don't remember the option, but it's something like azure analysis services SQL Server or something
I think we always want to be sure that the numbers are correct. The easiest way is excel
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.
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?)
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.
I actually don't experience this and it's been a major pain point for our Users. I seem to get a random selection of Datasets to choose from and after a recent update, the User now needs to know the name of the Dataset to search for it (and press enter) to return the results. I have 3 Certified Datasets, 1 of which does not appear at all in Get Data unless I search for it.
Any advice?
@@kylec7973 definitely not my experience. We have a P3 and a P2 tenant. Datasets we flag as Certified appear first, then Promoted, followed by no-tags.
@@rustedrootbeer That would be ideal. We have a P1 Capacity, but my selection of Datasets is frequently random.
@@rustedrootbeer FYI, I did get to talk to an engineer about this, and they said it's not intentional for Promoted/Certified to appear first, it's supposed to be in order of Usage. They are working on implementing Promoted/Certified sorted first.
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.
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!
You can drill the pivot table to transaction-level detail :)
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
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.
Excuse my insulance, but AiE? What’s AiE?
@@Cthames123 Probably a terrible abbreviation aka AnalyzeInExcel:)
@@TorgeirLognvik Thanks! That one had me miffed.
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.
True about cube formulas! Mind sharing the Hopkins link?
@@FrostySnowJr I presume I can't spam the comments with links to other content, but if you search for the Access Analytic YT channel it's one of the most recent videos
Agreed! Cube formulas are one of Microsoft's best kept secrets. Shouldn't be that way
@@FrostySnowJr here! 🙋🏻♂
We face huge latency when filtering data in analyze in excel option, so looking for other ways to get data using VBA.
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
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?
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.
Formulas do not work identical in PBI and Excel, so I guess this would only lead to confusion
@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?
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?
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?
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.
Agree on this one…Sometimes also, this is a bad idea, especially if you don’t want the end user having access to the complete data set for reasons of confidentiality or privileges. You may only intend for them to have access to the data presented in the particular visual. Is it possible to restrict the users access to the complete data set, so that it is excluded from their view in analyze in excel?
@@Cthames123 doesn't Analyse in Excel retain rls settings at least? (agree on the rest though)
I have found the answer to my own question….You can hide the tables in desktop mode before publishing to the Power BI service to make them inaccessible to end users in Analyze in Excel.
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.
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
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
I tihnk you should talk a bit more with end users before you start developing the report. Try to understand why they are doing this. Why they need this information. What they trying to achieve.
"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
I love to bring the featured tables of dataset via Excel datatypes! This is a simple stupid way to bring data into Excel :)
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
@Artur - You don't. You can use Perspectives on Analyze in Excel. Though Excel doesn't give you a drop down of all perspectives of the dataset, you can do it. The trick is you need to edit the connection properties and replace name of the Cube from Model to the name of your perspective (refer this image i.imgur.com/oEb7mry.png). This is not a great solution, as the user may not have an idea of the names of all perspectives :(
@@project365 You know - it works for me, but user wants clean pivot table ready model out of the box. Well there should be addtional option Visible/Hidden/Hidden in Analyze in Excel for every tabular object
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.
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.
*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?
😂😅😂 surprised they let you know of their intention to use VBA.
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?
If you found the answer could you share it, please?
Is every dude who became a "data analyst" a bald dude with square glasses and a thin graying beard?
👏🏾👏🏾👏🏾
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.
Developers think of best practice from their viewpoint, not how user friendly it is. Users may not trust the totals or may want to compare/validate the figures against another source, users may not see the formulae they want, they may want to different filters or a subset of data, users may want to add comments. I automate as much as I can, see how they use the data and what they do in excel to automate that too. I have also taught users to use power query. However, sometimes they just prefer interacting with excel, the same way I prefer drawing diagrams and mindmaps on paper when good practice may be to use apps to do this.
Is that pro?