Forum Discussion

pthapa's avatar
pthapa
Post Patron
2 years ago
Solved

How to remove the table name in column headers in Excel?

Hello all,
Excel sheet is connected to power bi dataset and when the table is loaded, it adds the table name on suffix and the column name. How can we not have the table name on the column headers?
In the screen shot below, the highlited portion in yellow is the table name in PBI which I don't need it. I just wat the column name.
Thanks in advance.
pthapa

 

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Select your semantic model and add it to a table

     

    Next, create a new query "from table"

    add the step into the query

     

    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

     

    Click "Close and Load".

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Table.TransformColumnNames(Source,each if Text.End(_,1)= "]" then let p=Text.PositionOf(_,"[") in Text.Range(_,p+1,Text.Length(_)-p-2) else _ )

  • pthapa's avatar
    pthapa
    Post Patron
    2 years ago

    Hello lbendlin ,
    Thanks for your quick response to this topic.
    I am confused with it, where am I applying the transformation steps in Excel or in power query in power bi dataset where the data is coming from?

    Thanks,

      • pthapa's avatar
        pthapa
        Post Patron
        2 years ago

        My power query is not coming up in Excel? It is greyed out.
        I did the following steps:
        File>Options>Add-ins>Manage>COM Add-ins>Go>Check Power Pivot for Excel
        Still power query is not coming up?

         

  • pthapa's avatar
    pthapa
    Post Patron
    2 years ago

    Awesome, it did work. I hope it will not break when I publised this to sharepoint folder.

    Appreciate your help lbendlin.

    Thank you.

    pthapa

  • SusieQ's avatar
    SusieQ
    Frequent Visitor
    1 year ago

    For those wondering where to paste the code, click Advanced Editor.