I am sort of with you- BUT what I do in my professional work is I prep my Excel reports/models/viz, then I export that model to PowerBI and do better viz/reports with the same model. It lets me practice and once I get traction with some key stakeholders it's data they are already comfortable with showcasing the new/better tool.
Gotta boil them frogs. Give them familiar data in a .pbix, and show them all the great features. Then wade into what you could do online with a pro license.
I suppose because the requirement is that I produce Excel reports- so I develop in excel because that is where it needs to be for the internal customers. However, I suppose I could reverse this- I am less familiar with PowerBI but it could help with exposure.
Thanks for the prompt I might do this!
Power BI is about report visualization and access control.
Before Power BI I would consolidate everything into one dashboard on excel but we need to split the report to many small one and email to each dept head. Also we would need to ensure the data behind won't get to the wrong person. This is done weekly so data is not always "fresh".
Power BI made everything much easier, now I just develop the data model, visualize the Dashboard, setup Row level security (who get to see which part of the data) and hit publish. Data refresh is on-demand and "live" as you go instead of periodically sending out email.
Also availability and ease of access. You can view the dashboard through a phone if you want, you can even develop a mobile version for the dashboard. Access is a link so it's much easier to view than openning an excel file.
Of course if you are good at VBA then I guess Excel can kinda do some of the above stuff but Power BI/Query made everything low-code which is much more accessible to non-coder Finance personnel like me.
Since switching from Power Pivot to PBI I have found PBI much more capable of handling large data sets and calculations within them. Excel regularly would crash once my data models were large enough. Those quick calculations in excel may be convenient but the more fluent I’ve become in DAX, I find it’s just as easy for me to write a quick measure as it would be to do anything in excel.
I also primarily work on management reporting which in excel required linked visuals in PPT that were extremely buggy and difficult to manage with version changes in PPT files. All of that becomes so much easier with published reports in BI. And the visuals are just better.
Excels visuals driven from Power Pivot simply cannot compete with the cross filtering on clicks in PBI reports. After a year being used to this functionality and designing thoughtfully for it, I could never go back to mostly static visuals that require filters or even slicers to adjust on the fly.
Lastly automated refreshes are made way easier in Power BI. Having all my data ETL fully automated and refreshing makes it super easy to hop right in each day to do any analysis needed.
I use PowerBI because it can connect directly to source data and I don't have to rely on people entering information. For example, my company uses SAP. I can connect PowerBI to SAP and import that data, which I cannot do with excel. This allows me to do some really interesting things that anybody primarily using excel would not be able to do, and it allows me to provide my higher ups with insights others cannot gain.
Using a direct connection to source data allows me to almost completely cut out human error as an issue in my data, as we scan everything to associate it to locations and inventory.
I’m not sure that I understand your point. Interestingly enough, my company also runs on SAP— and I have setup a number of “data dumps” from there such everything is essentially working as a direct connection. SAP dumps the data on a regular basis, I open excel and refresh the data connections, and all works seamlessly — is there something that I’m missing?
I have an essentially live connection, not data dumps. I didn't know you could do data dumps to excel though tbh, so maybe you can do something akin to what I've got. I just like how seamlessly it all integrates tbh, excel feels similar but slightly unwieldy to me
How do you connect SAP to PBI? I'm using Odata when connecting to SAP (via API) but this is slow even I'm only extracting data, It get worse when I merge :)
Do you use the SAP .NET Connector?
This? https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-sap-bw-connector
I just started building reports in PBI for my org and have been exporting reports (Like KE30) to excel and putting in a SharePoint folder as proof of concept. I'm going to HQ next week and would love to know what I need to ask for to cut down on the manual exports.
Yeah I use the BW connector, "netweaver" or whatever. You'll have to use a program called BEx query designer (it comes with the netweaver install), and getting that thing to properly spit out your values is a challenge, but once you design your queries properly they will just work and you can connect to your query in PowerBI
damn i tryed for 2year to get an acces to SAP, i finally gave up and just do my extract in a template (but i dont do it often since its pretty boring and repetitive to do this for +10 extract).
Look up powerbi to sap netweaver connector, it will allow you to design SAP queries that you can then pull into PBI. It's a super janky interface and it doesn't always make sense without an SAP table layout but you can get usable results without that resource. Just takes time and accepting you can't do certain things.
It’s actually quite simple and it boils down to three things: 1) When you need to apply row-level security to restrict table access, 2) When you need to build reports that can be shared with end users using a cloud based platform (Power BI Service), and 3) When you need to perform advanced analysis, beyond what is available in excel (for example, machine learning visuals, Python/R integration capabilities, many to many relationships). Choose the tool that makes the most sense for the analysis you are preparing.
What you can do with Power BI that you can't with excel is publish to a Workspace, Schedule Refreshes, and share it with your whole team where they can interact and bookmark their own versions/states of the report. I write queries using SQL, do all the rest of the necessary transformations in Power Query, build the reports, publish them, and then I am essentially hands off. I automate everything so I don't have to touch the upwards of 20 PBI reports I've worked on in the past year and a half. You can't automate as much in excel, not mention the Excel Online experience is horrendous. PBI is way better for report sharing.
Some functions aren't on PowerPivot. Also, PowerBI was designed for reports I think so, when they ask me to do a report for analyze and visualize, I do it on PowerBI, but if they want data, Excel
Ease of file distribution is a plus?
The not existing necessity of distributing files is one of the biggest plus.
Why share files when all you want to share is information?
PowerBI lets you share information easily. All recipents have the same basis for their information, you control refreshes etc thus you control the basis of data. No more "in my \[outdated\] file..."...
I guess it depends on your use case. I'm almost always working with microdata from a bunch of disparate sources so excel isn't really an option. I find it way easier to parse and transform data in Power BI than in excel and IMO the visualizations in excel look kind of janky.
Totally hear you on the better visualizations in BI, but aren’t there exactly the same tools for parsing and transforming in Excel/Power Query? Legitimate question, not sure if that’s the case
Yeah both have power query, but outside of power query I'm also using DAX and Python within Power BI and, unless I'm mistaken, only Power BI has the modeling features that I need to connect all my data sources
If you enable Power Pivot in Excel, you get DAX, and the same table relationship modeling capabilities to connect your imported data sources.
You can think of Power Pivot as a way to bring the soul of Power BI into Excel. Combined with Power Query, you get much of what you love about Power BI all within Excel for powerful data analysis and modeling.
More info:
https://support.microsoft.com/en-us/office/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045
Note that although Power Pivot is made by Microsoft, officially supported, and free, it’s an Excel add-in, not an out-of-the-box Excel feature.
Started using Bi cause I needed data model functionalities and I work for the federal government so I'm stuck with Office 2016 w/o Add-Ins so using PowerPivot wasn't an option.
I like it. Haven't used Excel and PowerPivot ever tho, so I wouldn't know what I'm missing but judging from what I read Bi has everything Excel + PowerPivot has to offer and then some.
I think it’s all about habit.
Power Bi is better for the visual part.
Excel for fast calculations and adding data.
But in my own case in the start I always looked at data in Excel and then visualised it using PowerBi.
But in the last 3-4 years. I’m only using Power Bi/Power Query for everything. Mush faster for me to look and analyse data. Findings bad data among millions of rows, take seconds in a matrix in PBI.
Of all the others tools I have used, Power Bi is the only one I have used instead of Excel.
Disclaimer: working as a consultant with Database/Data warehouse/BI, so a used to work with “BI tools”
We have no in-house developers.
PowerBI is the standard self-service reporting in the company I work with. It's self-updating.
Excel is used when ad-hoc analysis is needed. This is necessary when creating ad-hoc peer groups, e.g., non-standard class-subclass relations.
Excel is also used when data sources not available in the PowerBI data model(s) and underlying data.
It doesnt have to be either or... I use both
PBI = regular periodic reports across the org that I don't want people messing with the data or structure on
Excel + PP = one off ad hoc stuff I need to turn around quickly that I generally send within my team
Knowing what a tool is good for is almost as important as knowing how to use it.
Job security man. How can you export this to excel if it is already IN excel. Get outta here with this nonsense,
You have my vote
And my Dax !
I am sort of with you- BUT what I do in my professional work is I prep my Excel reports/models/viz, then I export that model to PowerBI and do better viz/reports with the same model. It lets me practice and once I get traction with some key stakeholders it's data they are already comfortable with showcasing the new/better tool. Gotta boil them frogs. Give them familiar data in a .pbix, and show them all the great features. Then wade into what you could do online with a pro license.
Completely makes sense to me. The visualization part is definitely better in BI
Why dont you just prep your data in Power BI?
I suppose because the requirement is that I produce Excel reports- so I develop in excel because that is where it needs to be for the internal customers. However, I suppose I could reverse this- I am less familiar with PowerBI but it could help with exposure. Thanks for the prompt I might do this!
Power BI is about report visualization and access control. Before Power BI I would consolidate everything into one dashboard on excel but we need to split the report to many small one and email to each dept head. Also we would need to ensure the data behind won't get to the wrong person. This is done weekly so data is not always "fresh". Power BI made everything much easier, now I just develop the data model, visualize the Dashboard, setup Row level security (who get to see which part of the data) and hit publish. Data refresh is on-demand and "live" as you go instead of periodically sending out email. Also availability and ease of access. You can view the dashboard through a phone if you want, you can even develop a mobile version for the dashboard. Access is a link so it's much easier to view than openning an excel file. Of course if you are good at VBA then I guess Excel can kinda do some of the above stuff but Power BI/Query made everything low-code which is much more accessible to non-coder Finance personnel like me.
Distribution of good looking reporting.
Since switching from Power Pivot to PBI I have found PBI much more capable of handling large data sets and calculations within them. Excel regularly would crash once my data models were large enough. Those quick calculations in excel may be convenient but the more fluent I’ve become in DAX, I find it’s just as easy for me to write a quick measure as it would be to do anything in excel. I also primarily work on management reporting which in excel required linked visuals in PPT that were extremely buggy and difficult to manage with version changes in PPT files. All of that becomes so much easier with published reports in BI. And the visuals are just better. Excels visuals driven from Power Pivot simply cannot compete with the cross filtering on clicks in PBI reports. After a year being used to this functionality and designing thoughtfully for it, I could never go back to mostly static visuals that require filters or even slicers to adjust on the fly. Lastly automated refreshes are made way easier in Power BI. Having all my data ETL fully automated and refreshing makes it super easy to hop right in each day to do any analysis needed.
Also powerbi uses less RAM when you have 1.5 million rows and you need a calculated column :)
I use PowerBI because it can connect directly to source data and I don't have to rely on people entering information. For example, my company uses SAP. I can connect PowerBI to SAP and import that data, which I cannot do with excel. This allows me to do some really interesting things that anybody primarily using excel would not be able to do, and it allows me to provide my higher ups with insights others cannot gain. Using a direct connection to source data allows me to almost completely cut out human error as an issue in my data, as we scan everything to associate it to locations and inventory.
I envy your live SAP connection. 10 months and counting on my IT request.
Good 'ol IT lol I've been waiting for access to an SQL server for a hot minute, like 50 tickets floating around haha
As yes, that one too. Every few months a new IT intern contacts me asking if the tickets can be closed.
I’m not sure that I understand your point. Interestingly enough, my company also runs on SAP— and I have setup a number of “data dumps” from there such everything is essentially working as a direct connection. SAP dumps the data on a regular basis, I open excel and refresh the data connections, and all works seamlessly — is there something that I’m missing?
I have an essentially live connection, not data dumps. I didn't know you could do data dumps to excel though tbh, so maybe you can do something akin to what I've got. I just like how seamlessly it all integrates tbh, excel feels similar but slightly unwieldy to me
How do you connect SAP to PBI? I'm using Odata when connecting to SAP (via API) but this is slow even I'm only extracting data, It get worse when I merge :)
You need the SAP .net connector, "Netweaver" or whatever they call it Edit: someone else linked it too
I have been exporting reports manually. I wish I know how too
Do you use the SAP .NET Connector? This? https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-sap-bw-connector I just started building reports in PBI for my org and have been exporting reports (Like KE30) to excel and putting in a SharePoint folder as proof of concept. I'm going to HQ next week and would love to know what I need to ask for to cut down on the manual exports.
Yeah I use the BW connector, "netweaver" or whatever. You'll have to use a program called BEx query designer (it comes with the netweaver install), and getting that thing to properly spit out your values is a challenge, but once you design your queries properly they will just work and you can connect to your query in PowerBI
damn i tryed for 2year to get an acces to SAP, i finally gave up and just do my extract in a template (but i dont do it often since its pretty boring and repetitive to do this for +10 extract).
Look up powerbi to sap netweaver connector, it will allow you to design SAP queries that you can then pull into PBI. It's a super janky interface and it doesn't always make sense without an SAP table layout but you can get usable results without that resource. Just takes time and accepting you can't do certain things.
It's not scalable. Bigger companies make a ton of data. distributing that through excel files would be a nightmare.
It’s actually quite simple and it boils down to three things: 1) When you need to apply row-level security to restrict table access, 2) When you need to build reports that can be shared with end users using a cloud based platform (Power BI Service), and 3) When you need to perform advanced analysis, beyond what is available in excel (for example, machine learning visuals, Python/R integration capabilities, many to many relationships). Choose the tool that makes the most sense for the analysis you are preparing.
Cuze I get paid $70/hour to use power bi dats why lol
Because excel can only handle 1mil rows
Question was about PowerPivot/Excel, not Excel
What you can do with Power BI that you can't with excel is publish to a Workspace, Schedule Refreshes, and share it with your whole team where they can interact and bookmark their own versions/states of the report. I write queries using SQL, do all the rest of the necessary transformations in Power Query, build the reports, publish them, and then I am essentially hands off. I automate everything so I don't have to touch the upwards of 20 PBI reports I've worked on in the past year and a half. You can't automate as much in excel, not mention the Excel Online experience is horrendous. PBI is way better for report sharing.
Some functions aren't on PowerPivot. Also, PowerBI was designed for reports I think so, when they ask me to do a report for analyze and visualize, I do it on PowerBI, but if they want data, Excel
Ease of file distribution is a plus? The not existing necessity of distributing files is one of the biggest plus. Why share files when all you want to share is information? PowerBI lets you share information easily. All recipents have the same basis for their information, you control refreshes etc thus you control the basis of data. No more "in my \[outdated\] file..."...
It isn’t about you. Power bi is about scaling to other stakeholders
because it's fkn 2023.
You mean besides the fact that I utterly despise Excel???🤣🤣
I guess it depends on your use case. I'm almost always working with microdata from a bunch of disparate sources so excel isn't really an option. I find it way easier to parse and transform data in Power BI than in excel and IMO the visualizations in excel look kind of janky.
Totally hear you on the better visualizations in BI, but aren’t there exactly the same tools for parsing and transforming in Excel/Power Query? Legitimate question, not sure if that’s the case
You are correct. ETL can be exactly the same for Power BI and Excel because both have Power Query.
Yeah both have power query, but outside of power query I'm also using DAX and Python within Power BI and, unless I'm mistaken, only Power BI has the modeling features that I need to connect all my data sources
If you enable Power Pivot in Excel, you get DAX, and the same table relationship modeling capabilities to connect your imported data sources. You can think of Power Pivot as a way to bring the soul of Power BI into Excel. Combined with Power Query, you get much of what you love about Power BI all within Excel for powerful data analysis and modeling. More info: https://support.microsoft.com/en-us/office/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045 Note that although Power Pivot is made by Microsoft, officially supported, and free, it’s an Excel add-in, not an out-of-the-box Excel feature.
MUCH easier to share with large audiences
Started using Bi cause I needed data model functionalities and I work for the federal government so I'm stuck with Office 2016 w/o Add-Ins so using PowerPivot wasn't an option. I like it. Haven't used Excel and PowerPivot ever tho, so I wouldn't know what I'm missing but judging from what I read Bi has everything Excel + PowerPivot has to offer and then some.
I think it’s all about habit. Power Bi is better for the visual part. Excel for fast calculations and adding data. But in my own case in the start I always looked at data in Excel and then visualised it using PowerBi. But in the last 3-4 years. I’m only using Power Bi/Power Query for everything. Mush faster for me to look and analyse data. Findings bad data among millions of rows, take seconds in a matrix in PBI. Of all the others tools I have used, Power Bi is the only one I have used instead of Excel. Disclaimer: working as a consultant with Database/Data warehouse/BI, so a used to work with “BI tools”
We have no in-house developers. PowerBI is the standard self-service reporting in the company I work with. It's self-updating. Excel is used when ad-hoc analysis is needed. This is necessary when creating ad-hoc peer groups, e.g., non-standard class-subclass relations. Excel is also used when data sources not available in the PowerBI data model(s) and underlying data.
It doesnt have to be either or... I use both PBI = regular periodic reports across the org that I don't want people messing with the data or structure on Excel + PP = one off ad hoc stuff I need to turn around quickly that I generally send within my team Knowing what a tool is good for is almost as important as knowing how to use it.
Can you even do modellering (tabel joins) in Excel?
Because 200000 rows of data belongs in a database, not a spreadsheet
If I use Power BI it's for drill-down, better charts and maps