Replace hard coded column reference with dynamic reference

[Anonymous]

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 mergeHeaders are used to merge/concatenate these columns in the Power Query. 

BlanksToNull = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,Table.ColumnNames(#"Trimmed Text")),
#"Merged Columns" = Table.CombineColumns(BlanksToNull,mergeHeaders[newColHeader], each Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(List.RemoveNulls(_)),"Merged")

I can dynamically change the header "Merged" of the new column made by #"Merged Columns" with the following

BlanksToNull = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,Table.ColumnNames(#"Trimmed Text")),
MyMergedHeader = Table.ColumnNames(mergeHeaders){0},
#"Merged Columns" = Table.CombineColumns(BlanksToNull,mergeHeaders[newColHeader], each Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(List.RemoveNulls(_)),MyMergedHeader),

But this is still hardcoded

mergeHeaders[newColHeader]

I have tried to replace it with

mergeHeaders[MyMergedHeader]

but I get the error

"Expression.Error: The column 'newColHeader' of the table wasn't found"

How do I do this?

Thank you

[v-jingzhang · Accepted solution]

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]

Thank you this worked great

[Anonymous]

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