I'm trying to import a table from a semantic model into Excel. I can get the data, but I need the same column headers that are present in the Power Bi Report, and the data via Queries and Connections shows the raw column headers with no obvious option to edit them.
I found a way to edit the headers in the Dax code;
-> Queries and Connections
-> Properties
-> Defintion
-> Command text:
I rename the columns, by editing the SELECTCOLUMNS statement. Following is an example:
DEFINE
VAR __DS0Core =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'TABLE'[COLUMN],
"CountRowsSCE", COUNTROWS('TABLE')
)
),
NOT(ISBLANK('TABLE'[COLUMN]))
)
),
"NEW COLUMN NAME", 'TABLE'[COLUMN]
)
VAR __DS0BodyLimited =
TOPN(
1000000,
__DS0Core,
"NEW COLUMN NAME",
1)
EVALUATE
__DS0BodyLimited
ORDER BY
"NEW COLUMN NAME"
However, the issue is, when the table is viewed in Excel the column header is always wrapped in [ ] eg: [NEW COLUMN NAME]
This excel spreadsheet is to be used by power automate for looking up data, and the column headers won't be recognised with the extra [ ].
Does anyone have a code solution to this?
A secondary issue is that the column order is changed whenever you edit a column, it disapears and appears at the end of the table, this seems to overide the column order listed in the query. Not sure why this is, seems to be a bug.
