Table.RenameColumn from an index of another table

[UKNSI-Powerbi]

 Hi all,

I have an issue how to manage more dynamically columns heades in a table.

I have table 1 with current month, next month and next month +1. In a separate (table 2) I was able to select month I would like to appear. 

MONTH

Aug 2022

Sep 2022

Oct 2022

I would like to have table 1 as the following

Table1.CurrentMonth = Aug 2022 Demand

Table1.NextMonth = Sep 2022 Supply

Table1.NextMonth1 = Oct 2022 Textxyz

[v-jingzhang · Accepted solution]

Hi @UKNSI-Powerbi 

Assume you only have the three months in the separate table 2, you can try my method shown as below. 

Table2: add a date type column and sort it ascendingly. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNVzAyMDJSitWJVgpOLUBw/JNLoJxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MONTH", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "MONTH", "Month Start"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Month Start", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Month Start", Order.Ascending}})
in
    #"Sorted Rows"

Table1: get sorted MONTH column from Table2, add corresponding suffix strings to each month value, and rename selected columns. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1MACRpiDSDMiOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, CurrentMonth = _t, NextMonth = _t, NextMonth1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"CurrentMonth", Int64.Type}, {"NextMonth", Int64.Type}, {"NextMonth1", Int64.Type}}),
    #"Table2 Months" = Table2[MONTH],
    #"New Column Headers" = List.Transform(List.Zip({#"Table2 Months", {"Demand", "Supply", "Textxyz"}}), each Text.Combine(_, " ")),
    #"Rename Columns" = Table.RenameColumns(#"Changed Type", List.Zip({Table.ColumnNames(Table.SelectColumns(#"Changed Type", {"CurrentMonth", "NextMonth", "NextMonth1"})), #"New Column Headers"}))
in
    #"Rename Columns"

Hope this helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.