Connecting Power BI (browser) to excel via OLEDB (not Analyze in Excel / pivot tables)

[lupinidas]

Hi All,

Is there a way to connect (via OLEDB or ODBC) reports created in Power BI to excel not utlizing "Analyze in Excel". I am trying to get flat tables instead of the pivot tables generated in Analyze in Excel.

For context, my firm utlizes a software/database for financial reporting. The software firm is moving the report writing from QlikView to Power BI. Right now all our reporting is completed by exporting data from the database, which is accessed online, in excel and copy/pasting said data into excel workbooks which populate formatted excel reports. I would like to cut out the copy/paste and establish a direct connection from Power BI into Excel.

[lupinidas]

Thanks Ankit!

Yes, that is what I am looking for, but I am not sure of the steps to establish the connection, i.e. refreshable tables in excel that are not in pivot table format. I see Get Data, but should I be connecting to a "Database" or possibly "Other Sources"? If I click on Get Data from Power BI, it comes through as a pivot table.

[AnkitKukreja]

Hi! @lupinidas 

I think if you'll use the Db it would show the data in the same format. Ideally, you might have to do an additional step by creating a table through the pivot and then maybe copy-paste that into another sheet.

I'm not sure what business problem you are trying to solve. If you could help me a bit with that me or others might be able to help you.

[lupinidas]

Thanks. A little context below.

The business objective is to streamline how we are generating reports. This includes both client facing deliverables and internal reconciliations. Both of which are completed in excel. The raw data is housed in a database. That database is now being connected to Power BI. While a big part of using PBI will be to create reports and analyze data directly in PBI, there is a mountain of other reports/workbooks already created/formatted in excel. These are templates/skeletons that are run by exporting an excel file from the database >> then copying and pasting into said workbooks. Through sumifs and vlookups, the data populates the reports. Given the sheer size of some of these files and the number of times we have to export data & Copy/Paste, from a business persepctive this is incredibly inefficient. Therefore, the goal is pull datasets (database is already connected to PBI) into excel via refreshable flat tables.

I'm 99% positive this can be done (if PBI can connect to excel via a pivot table - why not a flat table), but I'm lost on how to establish the connection string > is it OLEDB, or ODBC, or something else? Not sure how to find the server or database that PBI is on, or if I even need to do that.

[AnkitKukreja]

Hi! @lupinidas 

Try connecting to your DB. I don't have DB details handy, but I just connected to an excel file through get data and I expect you should see a query editor once connect as we see in PBI.

Please give it a try and let me know how it goes?

[AnkitKukreja]

Hi! @lupinidas 

I think you can directly connect to that dataset in excel. Excel also supports get data options like Power BI. Not sure if this what you are looking for?