Bring Power BI and Excel together

  Рет қаралды 38,756

Guy in a Cube

Guy in a Cube

Күн бұрын

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

Пікірлер: 87
@benrogers9092
@benrogers9092 Жыл бұрын
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.
@jeffnixon4868
@jeffnixon4868 Жыл бұрын
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.
@Allday1219
@Allday1219 11 ай бұрын
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.
@NormRidg
@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
@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.
@mohammadassaf6710
@mohammadassaf6710 Жыл бұрын
You can achieve that with field parameters in powerbi
@i8aNooB
@i8aNooB Жыл бұрын
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
@sands7779
@sands7779 Жыл бұрын
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.
@brijendraydv
@brijendraydv Жыл бұрын
Biggest reason is no write back capability in PBI,users want to enter manual inputs and comments on the downloaded data.
@dhruvinshah4817
@dhruvinshah4817 Жыл бұрын
You can build a Power App that writes back to the data source and embed the app into your Power BI Report.
@FrostySnowJr
@FrostySnowJr Жыл бұрын
@@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)
@brijendraydv
@brijendraydv Жыл бұрын
@@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😁
@biggbruvvtv
@biggbruvvtv Жыл бұрын
Exactly this!
@utubeAgape
@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!
@dhruvinshah4817
@dhruvinshah4817 Жыл бұрын
Agreed, sometimes it's just not easy to trust the accuracy of a measure/calculation created in DAX.
@FrostySnowJr
@FrostySnowJr Жыл бұрын
Very true, though dax Studio also helps on that front
@utubeAgape
@utubeAgape Жыл бұрын
@@FrostySnowJr I’d like to get DAX Studio👍
@akpfeiffer
@akpfeiffer Жыл бұрын
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.
@gonzalorengifo1031
@gonzalorengifo1031 Жыл бұрын
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
@alfredlear4141
@alfredlear4141 Жыл бұрын
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.
@GusCaravalho
@GusCaravalho Жыл бұрын
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.
@davejl8982
@davejl8982 Жыл бұрын
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.
@rustedrootbeer
@rustedrootbeer Жыл бұрын
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.
@kylec7973
@kylec7973 Жыл бұрын
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?
@rustedrootbeer
@rustedrootbeer Жыл бұрын
@@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.
@kylec7973
@kylec7973 Жыл бұрын
@@rustedrootbeer That would be ideal. We have a P1 Capacity, but my selection of Datasets is frequently random.
@kylec7973
@kylec7973 Жыл бұрын
@@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.
@ThePPhilo
@ThePPhilo Жыл бұрын
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?)
@Wardog36
@Wardog36 Жыл бұрын
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.
@thenewdataguy
@thenewdataguy Жыл бұрын
Thanks! I think people want to have it in excel to make some calculations which are not represented in PBI Report..
@kattyhatty3799
@kattyhatty3799 Жыл бұрын
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.
@NickM273
@NickM273 Жыл бұрын
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.
@FrostySnowJr
@FrostySnowJr Жыл бұрын
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
@kasperkaspersen361
@kasperkaspersen361 Жыл бұрын
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!
@GusCaravalho
@GusCaravalho Жыл бұрын
You can drill the pivot table to transaction-level detail :)
@johnnyw5627
@johnnyw5627 Жыл бұрын
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.
@FrostySnowJr
@FrostySnowJr Жыл бұрын
True about cube formulas! Mind sharing the Hopkins link?
@johnnyw5627
@johnnyw5627 Жыл бұрын
@@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
@FedericoLov
@FedericoLov Жыл бұрын
Agreed! Cube formulas are one of Microsoft's best kept secrets. Shouldn't be that way
@AccessAnalytic
@AccessAnalytic Жыл бұрын
@@FrostySnowJr here! 🙋🏻‍♂
@marcelo1689
@marcelo1689 Жыл бұрын
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.
@collontomlyn6567
@collontomlyn6567 Жыл бұрын
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.
@Cthames123
@Cthames123 Жыл бұрын
Excuse my insulance, but AiE? What’s AiE?
@TorgeirLognvik
@TorgeirLognvik Жыл бұрын
@@Cthames123 Probably a terrible abbreviation aka AnalyzeInExcel:)
@Cthames123
@Cthames123 Жыл бұрын
@@TorgeirLognvik Thanks! That one had me miffed.
@FRANKWHITE1996
@FRANKWHITE1996 Жыл бұрын
Thanks for sharing ❤
@ahmetyildirim7268
@ahmetyildirim7268 Жыл бұрын
I think we always want to be sure that the numbers are correct. The easiest way is excel
@cg24356
@cg24356 Жыл бұрын
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
@udusegbe
@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
@ivanpintodiasjunior3587
@ivanpintodiasjunior3587 Жыл бұрын
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.
@marcelo1689
@marcelo1689 Жыл бұрын
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.
@prawnyowl8909
@prawnyowl8909 Жыл бұрын
Formulas do not work identical in PBI and Excel, so I guess this would only lead to confusion
@juanpablorvvv
@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.
@Cthames123
@Cthames123 Жыл бұрын
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?
@FrostySnowJr
@FrostySnowJr Жыл бұрын
@@Cthames123 doesn't Analyse in Excel retain rls settings at least? (agree on the rest though)
@Cthames123
@Cthames123 Жыл бұрын
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.
@sajjad5189
@sajjad5189 Жыл бұрын
We face huge latency when filtering data in analyze in excel option, so looking for other ways to get data using VBA.
@awarrentfa
@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?
@tonylather6134
@tonylather6134 10 ай бұрын
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
@nikhil1888
@nikhil1888 Жыл бұрын
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
@user-yc2ow2nj3k
@user-yc2ow2nj3k Жыл бұрын
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.
@austinpeterson9595
@austinpeterson9595 Жыл бұрын
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?
@project365
@project365 Жыл бұрын
I love to bring the featured tables of dataset via Excel datatypes! This is a simple stupid way to bring data into Excel :)
@j_arjunrao
@j_arjunrao Жыл бұрын
Can I export the millions of rows from Power BI desktop now, when can we see that option? Thanks in advance
@joesmith4254
@joesmith4254 Жыл бұрын
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.
@vanlessing
@vanlessing Жыл бұрын
"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
@roncruise4
@roncruise4 Ай бұрын
@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?
@daviduriarte3399
@daviduriarte3399 10 ай бұрын
Any way to link a PowerBI table to Access?
@FrostySnowJr
@FrostySnowJr Жыл бұрын
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
@biggbruvvtv
@biggbruvvtv Жыл бұрын
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?
@Baldur1005
@Baldur1005 Жыл бұрын
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
@project365
@project365 Жыл бұрын
@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 :(
@Baldur1005
@Baldur1005 Жыл бұрын
@@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
@wisid
@wisid Жыл бұрын
sadly not for Mac...ms needs to catch up on macOS pbi
@juanpablorvvv
@juanpablorvvv Жыл бұрын
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.
@SetYourBarTo10
@SetYourBarTo10 Жыл бұрын
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.
@joshash5944
@joshash5944 Жыл бұрын
*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?
@sands7779
@sands7779 Жыл бұрын
😂😅😂 surprised they let you know of their intention to use VBA.
@johnadair4979
@johnadair4979 Жыл бұрын
Why am I not seeing "From Power BI" in my Get Data dropdown list?
@Tonitonichoppa_o
@Tonitonichoppa_o 2 ай бұрын
If you found the answer could you share it, please?
@younissyed9259
@younissyed9259 Жыл бұрын
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
@Will_Moffett
@Will_Moffett 10 ай бұрын
Is every dude who became a "data analyst" a bald dude with square glasses and a thin graying beard?
@flyingeagle3750
@flyingeagle3750 Жыл бұрын
👏🏾👏🏾👏🏾
@DataisKing
@DataisKing Жыл бұрын
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.
@sands7779
@sands7779 Жыл бұрын
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.
@michaelcheung4530
@michaelcheung4530 Жыл бұрын
Is that pro?
Handling MULTIPLE fact tables in Power BI
9:02
Guy in a Cube
Рет қаралды 294 М.
Create your first Power BI report (2021)
15:25
Guy in a Cube
Рет қаралды 229 М.
Универ. 10 лет спустя - ВСЕ СЕРИИ ПОДРЯД
9:04:59
Комедии 2023
Рет қаралды 1,2 МЛН
Cute Barbie Gadget 🥰 #gadgets
01:00
FLIP FLOP Hacks
Рет қаралды 52 МЛН
Please be kind🙏
00:34
ISSEI / いっせい
Рет қаралды 54 МЛН
Excel v Power BI compared
10:49
Access Analytic
Рет қаралды 15 М.
STOP publishing your Power BI report until you do these 5 things!
10:15
From Excel to Power BI in 12 Minutes (Beginner Essentials)
12:43
MyOnlineTrainingHub
Рет қаралды 29 М.
Power BI vs Excel: When you should use each tool
15:22
Curbal
Рет қаралды 17 М.
How to use Microsoft Power BI - Tutorial for Beginners
27:53
Kevin Stratvert
Рет қаралды 3,9 МЛН
How to Turn Your Excel Spreadsheets into Power Apps
32:45
Lisa Crosbie
Рет қаралды 241 М.
How To Create these useful Power Bi Visuals that Excel Lacks
10:55
Leila Gharani
Рет қаралды 432 М.
3 REASONS to use a single dataset for your Power BI reports
8:55
Guy in a Cube
Рет қаралды 154 М.
REDUCE the # of measures with Calculation Groups In Power BI
9:24
Guy in a Cube
Рет қаралды 214 М.
Power BI Apps for multiple audiences has arrived!
6:22
Guy in a Cube
Рет қаралды 83 М.
keren sih #iphone #apple
0:16
kadangaruan
Рет қаралды 1,6 МЛН
MacBook Air Японский Прикол!
0:42
Sergey Delaisy
Рет қаралды 236 М.
Дени против умной колонки😁
0:40
Deni & Mani
Рет қаралды 12 МЛН
Урна с айфонами!
0:30
По ту сторону Гугла
Рет қаралды 6 МЛН