Edit and preserve column headers in connected table

[jokrra89]

Hi,

I have an issue with changing column headers in a table in Excel.
after reading this Design refreshable reports in Excel with Power BI data - Power BI | Microsoft Learn

I found this to be a good approach for combining Power BI data in an Excel table.

The article states that you can customize the table and that the formatting will be preserved when I refresh data.
This does not work for me.

I tried the solution from @lbendlin in this post Solved: How to remove the table name in column headers in ... - Microsoft Fabric Community

But from my understanding, this requires the main table to be present in the Excel file. Leading to duplicate data.

Can anyone help me with this?

Best regards 
@jokrra89 

[v-mdharahman]

Hi @jokrra89,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-mdharahman]

Hi @jokrra89,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-mdharahman]

Hi @jokrra89,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help?

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[jokrra89]

Hi @v-mdharahman,
My issue is not really resolved, but @lbendlin helped clarify that his solutions is related to SSAS cube. 

My issue is related to Power BI Semantic model connection (XMLA) , and from the article by Microsoft
Design refreshable reports in Excel with Power BI data - Power BI | Microsoft Learn 
shows an example where you can change the column headers, and it should be preserved.

If anyone has a solution/clarification to this, it would be highly appreciated.

Best regards

[v-mdharahman]

Hi @jokrra89,

Thanks for reaching out to the Microsoft fabric community forum.

Your issue of changing column headers in a table in Excel can occur due toi various reasons:

* If the Power BI dataset structure changes (columns getting renamed), Excel will overwrite the   

   customized headers during refresh. Make sure the column names in the Power BI dataset remain

   unchanged. Try renaming the headers directly in Excel without deleting the original ones.

* Check the option to auto-format tables during refresh in Excel, which can overwrite your

   customizations. Go to Data and then Queries & Connections then right-click the query and select

   properties. Uncleck "Preserve column sort/filter/layout" and see if this changes the behavior. 

   Also try to uncheck it if its already checked and see if it behaves differently depending on the

   version.

* Also check your PowerBI Dataset Refresh settings as certain refresh behaviors in Power BI Service 

  may start structural refreshes in Excel. To check this verify that the dataset isn’t sending metadata

  updates that force Excel to reset headers if you are using Power BI datasets with incremental refresh.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @jokrra89,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you were facing issues related to column header in a table. If the solution provided by @lbendlin works for you then please accept it as solution.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[jokrra89]

I tried the solution from the post I referred to. 

Then I am able to remove the table name and the square brackets from the column name:

This is achievable using the M statement you provided:

let
Source = Excel.CurrentWorkbook(){[Name="Table_ExternalData_1"]}[Content],
Res = Table.TransformColumnNames(Source,each if Text.End(_,1)= "]" then let p=Text.PositionOf(_,"[") in Text.Range(_,p+1,Text.Length(_)-p-2) else _ )
in
Res

But for this to work, It needs to reference the table in the current workbook.

So, If I want to make this file refreshable, I need to keep both tables in the file, to be able to reference it in power query?

[lbendlin]

not sure what "both tables"  means here. It's a single query that has transforms applied to it.  Please elaborate.

[jokrra89]

I am connecting to a Power BI semantic model through Excel.
Then I run a Dax query to get a table into Excel.

This creates a table named Table_ExternalData_1. We can call this table nr 1

Then, using your trick, 
I create a blank query in power query, and Load to Excel.

This creates a table named Query1.
This we can call Table nr 2.

When I refresh the data, Table nr 1 needs to exist in the file for Table 2 to be updated. If i delete Table nr 1, then Table nr 2 will be empty.

[lbendlin]

Ah, you don't need Table_ExternalData_1.  In Excel you can choose to create queries that do not materialize into tables.  You can then decide later to create a table from the (now modified) query.

Look at the Table Source for Table Nr2.  It has a reference that you need to change.

[jokrra89]

Table_ExternalData_1 is not visible in Power Query, as it comes from a connection.
I am not sure about the correct Naming, but I guess from 
Design refreshable reports in Excel with Power BI data - Power BI | Microsoft Learn
that the Power BI data is connected via the XMLA endpoint.

[lbendlin · Accepted solution]

You need to distinguish between two scenarios

#1  Run a DAX query against a semantic model.  For that you connect to "Power BI Semantic Model".  You won't be able to change/remove the table names

#2 Import data from a SSAS cube ("Analysis Services Database"), by specifying the same DAX query.  This will result in a Power Query connection where you can then add steps to remove the table names and do other manipulations.

[jokrra89]

Hi,
Thanks for the clarification. 
I am using approach #1
A combination of hacks make it possible to remove the table names:
Define column names in a selectcolumns statement in the DAX query, 
use you Power Query approach to remove "[ ]".

My question is if there should be a native solution.

according to 
Design refreshable reports in Excel with Power BI data - Power BI | Microsoft Learn

Which refers to #1 

Anyway, thank you for your insight

[lbendlin]

But from my understanding, this requires the main table to be present in the Excel file. Leading to duplicate data.

Please elaborate.  Can you show an example?