Forum Discussion
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 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
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.
3 Replies
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
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.