Forum Discussion
Edit and preserve column headers in connected table
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.
I tried the solution from the post I referred to.
Then I am able to remove the table name and the square brackets from the column name:
This is achievable using the M statement you provided:
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
But for this to work, It needs to reference the table in the current workbook.
So, If I want to make this file refreshable, I need to keep both tables in the file, to be able to reference it in power query?
Super Usernot sure what "both tables" means here. It's a single query that has transforms applied to it. Please elaborate.
I am connecting to a Power BI semantic model through Excel.
Then I run a Dax query to get a table into Excel.
This creates a table named Table_ExternalData_1. We can call this table nr 1
Then, using your trick,
I create a blank query in power query, and Load to Excel.This creates a table named Query1.
This we can call Table nr 2.When I refresh the data, Table nr 1 needs to exist in the file for Table 2 to be updated. If i delete Table nr 1, then Table nr 2 will be empty.
- lbendlin
Ah, you don't need Table_ExternalData_1. In Excel you can choose to create queries that do not materialize into tables. You can then decide later to create a table from the (now modified) query.
Look at the Table Source for Table Nr2. It has a reference that you need to change.
Table_ExternalData_1 is not visible in Power Query, as it comes from a connection.
I am not sure about the correct Naming, but I guess from
Design refreshable reports in Excel with Power BI data - Power BI | Microsoft Learn
that the Power BI data is connected via the XMLA endpoint.