Forum Discussion
Edit and preserve column headers in connected table
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
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.
14 Replies
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?
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
ResBut 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?not sure what "both tables" means here. It's a single query that has transforms applied to it. Please elaborate.
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.
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 TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
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.Hi Anonymous,
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 regardsHi 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 TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
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.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.
