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

[pthapa]

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

[SusieQ]

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

[pthapa]

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

Appreciate your help lbendlin.

Thank you.

pthapa

[pthapa]

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,

[lbendlin]

in Excel Power Query.

[pthapa]

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?

[lbendlin · Accepted solution]

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".

[lbendlin]

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