How to Get Data from a Power BI dataset into a Excel table, and change all Headers

Question

I was working on a way to return a flat file table in Excel that query an existing dataset in Power BI- and rename all the headers on the fly. The two first steps is explained here, but I can`t find any good way to rename the headers. Normally this can be done in Power query. If I change name of the header. Refresh will then replace the header according to what is in the OLAP cube.

Today Get Data in Excel can connect to the dataset - But it can only return the dataset as a PivotTable.

I found a YouTube video that showed me a work-around. Import and set up the PivotTable with dimensions from two tables. Then drill into the data with the double click. If Excel-pivot generate a new worksheet, this will contain a new Query on the OLAP cube, with a Connection and Command Text.

Next step is to replace the Command text with the query found in Power BI (web). Goto the Data set - and pick the table (or any fields needed) - And copy the Query to clipboard.

Next you can replace the Command text with this query. And Whola - the flat file contains the entire table.

If anyone can find a nice way to rename headers on the fly (without use of VBA) it will be appreciated. Otherwise I hope this post can be used as a learning experience for someone.

Hopefully Microsoft will implement a Get Data from Power Bi as table option in Excel soon, also that can handle this via Power query where user can manipulate headers as well by use of some DAX expressions.

Answer 1

Ended up using VBA to clean up all the headers in all my sheets connected to the Power Bi (OLAP cube). I then perform a call to RenameAllOLAPSheetHeaders before using the data on other steps. That does a small test if data has been refreshed by looking for the '[' in one cell, and then do refresh of all sheet is present. Here is my code:

Sub RenameHeaders(sh As String)
' Removes tablename and keeep fieldname between [] from Header row
co = 1
With ThisWorkbook.Worksheets(sh)
Do Until .Cells(1, co) = ""
tmp = .Cells(1, co)
A = InStr(1, tmp, "[")
If A > 0 Then B = InStr(A, tmp, "]")
If B > A And A > 0 Then tmp = Mid(tmp, A + 1, B - A - 1)
.Cells(1, co) = tmp
co = co + 1
Loop
End With
End Sub
Sub RenameAllOLAPSheetHeaders()
TestVal = Worksheets("SAP-ordrer").Cells(1, 1)
If InStr(1, TestVal, "[") > 0 Then
    ' Then rename headers
    RenameHeaders ("SAP-ordrer")
    RenameHeaders ("SAP-Op-linjer")
    RenameHeaders ("ZIQ09")
    RenameHeaders ("CC-WS Responsible")
    RenameHeaders ("Project Responsibles")
End If
End Sub