Forum Discussion

jokrra89's avatar
jokrra89
Frequent Visitor
1 year ago
Solved

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 ...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    jokrra89
    Frequent Visitor
    1 year ago

    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 regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.