Power Query to Dynamically Change Column Name

[legalhustler]

Table1 has the Fiscal Year:

Fiscal Year

2021

Would like Table2 to dynamically change the column name from "Total" to "Total 2021 Amount" (as shown below) based on the fiscal year input in Table 1.

Station	Total 2021 Amount
2	$5
3	$10

Can it be done through the interface selections?

 

[Misca]

I don't think you can change it dynamically using the interface but if copy the first line from the following code to the beginning of your code you can refer to it later:

let
    FiscalYear = "Total " & Text.From(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[#"Fiscal Year"]) & " amount",
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Total", FiscalYear}})
in
    #"Renamed Columns"

 

[GraH]

Yes it is possible, if you don't mind updating the M code of one of the steps.

Query 1: drill down on the value and name the Query fiscalyear or so.

Query two: do the rename manually. Now in the fx-bar update the M-code to something like:

"Total " & fiscalyear & " amount"

Edit: @Misca 's solution is better, since it does not reference another query. Might be a bit more challenging for a beginner to insert a block of code before a query.

 

[legalhustler]

Yes it is possible, if you don't mind updating the M code of one of the steps.

Query 1: drill down on the value and name the Query fiscalyear or so.

Query two: do the rename manually. Now in the fx-bar update the M-code to something like:

[CODE=pq]"Total " & fiscalyear & " amount"

[/CODE]

Edit: @Misca 's solution is better, since it does not reference another query. Might be a bit more challenging for a beginner to insert a block of code before a query.

I got the following error message when I concatenated the query two expression:

We cannot apply operator & to types Text and Number.

Not sure what to fix.

 

[legalhustler]

I don't think you can change it dynamically using the interface but if copy the first line from the following code to the beginning of your code you can refer to it later:

let
    FiscalYear = "Total " & Text.From(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}[#"Fiscal Year"]) & " amount",
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Total", FiscalYear}})
in
    #"Renamed Columns"

This solution works. Thanks!

 

[GraH]

I got the following error message when I concatenated the query two expression:

We cannot apply operator & to types Text and Number.

Not sure what to fix.

Yes, forgot to mention you need to define fiscalyear as text before drilldown.