Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Replace hard coded column reference with dynamic reference

I have a table called mergeHeaders which has entries of headers of columns of a Power Query. The table mergeHeaders has one column and a header called "newColHeader"   The entries of mergeHea...
Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    You can use

    Table.Column(mergeHeaders,MyMergedHeader)

    to replace 

    mergeHeaders[newColHeader]

     

    The code is 

    MyMergedHeader = Table.ColumnNames(mergeHeaders){0},
#"Merged Columns" = Table.CombineColumns(BlanksToNull, Table.Column(mergeHeaders,MyMergedHeader), each Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(List.RemoveNulls(_)), MyMergedHeader)

     

    Reference: Table.Column 

     

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

So, I  could not figure this out, and there are no suggestions from the community, but I did come up with a workaround.

I created a named range consisting of a single cell  called "newheader" 

I then altered my query to this

 

#"Merged Columns" = Table.CombineColumns(BlanksToNull,mergeHeaders[newColHeader], each Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(List.RemoveNulls(_)),"Merged"),
newMergedHeader = Excel.CurrentWorkbook(){[Name="newheader"]}[Content]{0}[Column1],
#"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged",newMergedHeader}})

 

So now I can dynamically concatenate columns and give a meaningful header name without manually altering the Power Query