TableName[ColumnName] to ColumnName when Inserting Table from Semantic Model in Excel

[Dan44]

Hi team,

I'm inserting a table of data from my semantic model published in Fabric within Excel, through the following steps:

• On the Data tab click on Get Data > From Power Platform > From Power BI
• Select desired semantic model and choose Insert Table

My table looks like this:

TableName[ID]	TableName[Name]	TableName[Value]
1	Alice	100
2	Bob	200

However I want it to look like this each time it loads:

ID	Name	Value
1	Alice	100
2	Bob	200

If you manually change the column headers and the data gets refreshed however, it will revert back to default. Has anyone encountered this and have tips how to just show the original column name? 

Thanks

[Anonymous]

Hi all,thank you for your quick reply, I will add more.

Hi @Dan44 ,

You can insert a row before the column header and enter the column name you want. Then hide the column header.

After refresh

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dan44]

@Anonymous thanks but unfortunately there is an issue when I do that. The new header row will not filter the field properly. When I then try and expand the coverage of the table to include the new row, the following occurs: 

[SaiTejaTalasila]

Hi @Dan44 ,

I don't think you have much options left.If you want to try alternate approaches you can check this -

You can use run a query against dataset in power automate and you will be able to pull the data with user defined column names in dax.

You can load data to a dataflow and apply your transformations then your users can access the data from Excel and can import the data.

Thanks,

Sai Teja 

[Dan44]

@SaiTejaTalasila thanks for your suggestions. Is it the case however you cannot select a Dataflow Gen2 with the Get Data, From Power Platform, Dataflows? Essentially the issue I'm dealing with is I have the following Office Script which I am scheduling: 

function main(workbook: ExcelScript.Workbook) {
    workbook.refreshAllDataConnections();
}

The scheduling only works when my data is brought through by Analyze in Excel or Get Data, From PowerPlatform where I connect to the semantic model (which I am currently doing). If I however connect to the SQL Analytics Endpoint through Power Query (using the SQL string) of my lakehouse, and load the data to the worksheet, the above script will not run (at least when it is scheduled as it works when manually run). Note I have another active post on this issue:

https://community.fabric.microsoft.com/t5/Service/Auto-Refresh-Excel-Report-with-Power-Pivot-Data-Mo...
 

[SaiTejaTalasila]

Hi @Dan44 ,

If you are looking for an alternative approaches you can check my ideas they will be able to do the job.You check semantic link and I think you have fabric capacity based on your comments.

https://learn.microsoft.com/en-us/fabric/data-science/semantic-link-overview

Power automate run a query against dataset 

https://curbal.com/curbal-learning-portal/automatic-export-power-bi-data-to-excel-csv-run-a-query-ag...

In dax alias is possible 

NewTable = 

SELECTCOLUMNS(

    ExistingTable,

    "NewColumnName1", [ExistingColumnName1],

    "NewColumnName2", [ExistingColumnName

2]

)

Thanks,

Sai Teja 

[GilbertQ]

Hi there

Just remember that there is a limit for using the DAX query to export in Power Automate.

Unlocking new self-service BI scenarios with ExecuteQueries support in Power Automate | Microsoft Po...

The only other workaround is to use PowerShell or Fabric to extract the data.

[GilbertQ]

Hi @Dan44 

If you had to connect directly to the semantic model, you should be able to just get the name as you need or what is being displayed.

Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn

[Dan44]

@GilbertQ I'm aware thats an option thanks but I want to avoid that Analyze in Excel approach. Basically because it's a pivot table, unless the user specifically has access to the semantic model, when they try and manually filter the pivot table it will throw an error. Regardless of if there's permissions meanwhile, it will always throw an error if the Excel report is published to a workspace.

However it seems the price for pulling the data into the table option is to always display the table name in the headers...