Power BI service to Excel

[Anonymous]

Hi all,

Our ERP-system supplier recently created a data warehouse with all our internal data. 

We can create and publish power BI reports using the flat tables which are in the datawarehouse.

However, we still want to be able to get the flat tables to excel to analyze the 'raw' data and to do ad-hoc analyses. 

When I hit export in Excel in Power BI service I only get pivot tables.

Is there a way how I can get the flat table in the datawarehouse, which is imported in power BI to Excel?

[Burningsuit · Accepted solution]

Hi @Anonymous 

I think the easiest solution would be to use Power Query in Excel, Use "Get Data" in Excel and you'll see it's the same as Power Query in Power BI. Just connect to your data warehouse and you can load the flat tables in Excel.

Don't forget you've also got the Q&A visual in Power BI so you can give your users ad-hoc analysis through Power BI if you so wish.

Hope this helps

Stuart

[Anonymous]

Hi @Burningsuit 

That was exactly what I was thinking but it appears they simply want to easily select the table/dataset from Power BI service and then make the flat export. Is this possible? I can't imagine Power BI and Excel not being compatible. 

[Burningsuit]

Hi @Anonymous 

I don't think you can simply export a Table from a Dataset, but then why would you want to? That table of data has come from somewhere, via Power Query and been placed in the DataModel. You can do exactly the same in Excel get the data from wherever, via Power Query, then place it in an Excel sheet. So in Excel you get the "raw" data which you can then analyse (or turn into a pivot table 😀) With a bit of parametrization in Power Query you could almost make it user friendly. If you connect to a Power BI dataset with Excel it looks to leverage the Star Schema and returns the whole DataModel as a pivot table, so you don't get the raw data.

Hope this helps

Stuart