New Enterprise DNA Initiatives

Excel and PowerBI

I have used Excel for 20+years and would consider myself an advanced user. I have read about people who have used Excel for 20 years now hardly / never use it and instead use PowerBI. At present, I can’t imagine that even by different means PowerBI can do all that Excel can do. So I am very unsure when to use Excel and when to use PowerBI. My IT department is having difficulty get Excel connected to our databases but PowerBI seems to be working ok. I envisage that I will need to do an analysis of the data before then designing a PowerBI.

Is there a way I can, if necessary connect each time to my data sources using PowerBI and then from then on use Excel. Or is there a need just to stop using Excel and commit to using PowerBI.

I am interested in the views of far-more experienced people than myself.

Can someone direct me to some EDNA training or other sources that would help explain when to use Excel and when to use PowerBI.

thank you

Allister

@AllisterB hmm, strange… both of them have almost similar options to connect to a database.

I would never do that, I can’t think of using calculator for adding 2 numbers. Calculator doesn’t help in release of any serotonin. Lol.

=========================================================================

Either you connect to DAX Studio, extract the model into CSV files, import data into Power Pivot in Excel and start working.

or you could use the below mentioned tool for a live connection.

Analyze in Excel for Power BI Desktop - SQLBI

@AllisterB,

You may find this seminar that @sam.mckay did last week useful in addressing some of your questions:

I am one of those people you referenced to the top of your post – I’ve used Excel extensively since the late 1980s, but barely use it at all anymore except to bring data into Power BI. When I want to break down a complex measure or otherwise drop back into Excel, I typically use the Analyze in Excel external tool that @AntrikshSharma mentioned above:

I still occasionally use Excel for quick and dirty analyses, but for anything with some complexity to it, I now do it in Power BI from the start.

I hope that’s helpful.

  • Brian
1 Like

Hi Allister,

Great question! I get a similar question from our customers: “Our people really like to work with excel. Can we analyze the data in Power BI as in excel?”.

Yes! There is a possibility to transform the Power BI report to excel to perform analysis in excel. The option is under your report in Power BI Service under "Export - Analyze in excel.

Please note that this requires Power BI Service, which means you will (1) develop the report in Power BI desktop and (2) publish the desktop to Power BI Service (which is a paid, but cheap service). I adviced this option to a customer and it works well.

As per my knowledge, there is no possibility to modify a Power BI desktop file (.pbix) to excel.

Let me know if you need any further information.

Kind regards,
Rens

@AllisterB

To offer a slightly different perspective here, I still use Excel in my daily work life, but I also use PowerBi, and it is a delicate balancing act. As you have encountered, my IT department has had struggles getting a connection from Excel to our database, so instead I built a datasource file from dataflows, and connect to that datasource instead of attempting a live connection to my database.

I still use Excel frequently because I have users who really only need a flat file to review some data, or present to someone outside of our company. I use PowerBi desktop to author reports that my users still consider as ‘dashboards’, but they want the flat file from those as well, so I have included tables with detail data that they can export from the ‘dashboard’. :slight_smile:

I use YouTube quite a bit in my learning journey, and I have found that Oz du Soleil is a great ambassador of Excel using Power Query - he doesn’t showcase a lot of advanced DAX or M, but he does demonstrate why you might use Excel for some of your data crunching.

3 Likes

That used to be the case, but with the introduction in July 2020 of the External Tools menu, you can now analyze in Excel on the desktop as well using the SQLBi external tool (see my above post for link).

  • Brian

Hi @AllisterB, we’ve noticed that no response has been received from you since the 30th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!