Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Thank you this worked great
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.