Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a power query that parses a JSON into tabular form, the JSON data is dynamic
I need to merge/concatenate columns with a pipe delimeter. I can do this like
#"Merged Columns" = Table.CombineColumns(#"Trimmed Text",{"Header5", "Header1", "Header3", "Header22","Header8"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
But the column headers keep changing when the JSON changes.
I want to loop through a Table (called mergeHeaders) that has the header names to be merged and have these columns in the Power Query merged into a new column
I can't figure out how to loop through the table
Thanks in advance for help with this
Solved! Go to Solution.
Use this
#"Merged Columns" = Table.CombineColumns(#"Trimmed Text",mergeHeaders[Headers],Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
Hi @Anonymous ,
For dynamic columns, the easiest way is to use the Table.ColumnNames function in power query.
Table.ColumnNames - PowerQuery M | Microsoft Docs
Each time you load data, this function will create a dynamic list of your column names. All you need to do is use this list as a reference in your downstream steps wherever column names are referenced.
Here is an example :
https://wmfexcel.com/2021/04/18/rename-column-names-in-a-dynamic-way-with-excel-powerquery/
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
This is helpfe]ul in itself, thank you (but I could not see how to apply this to this question)
Use this
#"Merged Columns" = Table.CombineColumns(#"Trimmed Text",mergeHeaders[Headers],Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
Hello Vijay,
I can not get it to work. I get this error
even though the Table has the proper name
I want to see your complete query.
Here it is (everything works up to the last step)
let
sourceLink = Excel.CurrentWorkbook(){[Name="sourceLink"]}[Content]{0}[Column1],
Source = Json.Document(Web.Contents(sourceLink)),
Expanded=Table.FromRecords (Source[elements] ),
#"Removed Errors" = Table.RemoveRowsWithErrors(Expanded),
#"Added Index" = Table.AddIndexColumn(#"Removed Errors", "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Combine items in list" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", each try if Value.Is(_, type list ) then Text.Combine(_,"|") else _ otherwise _, type text}}),
#"Pivoted Column" = Table.Pivot(#"Combine items in list", List.Distinct(#"Combine items in list"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",List.Transform(Table.ColumnNames(#"Removed Columns"),each {_,type text})),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Id", "Label", "Email", "Project Name", "Type", "Segment", "Tags", "Description", "First Name", "Last Name", "Email 2", "Email 3", "Initial Date", "Last Date", "Master ID", "sumApp Link", "Image"},MissingField.Ignore),
#"Demoted Headers" = Table.DemoteHeaders(#"Reordered Columns"),
#"Replaced Value" = Table.ReplaceValue(#"Demoted Headers","#(00A0)","",Replacer.ReplaceText,Table.ColumnNames(#"Demoted Headers")),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(0087)","",Replacer.ReplaceText,Table.ColumnNames(#"Replaced Value")),
#"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value1", [PromoteAllScalars=true]),
#"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Label", Order.Ascending}}),
#"Trimmed Text" = Table.TransformColumns(#"Sorted Rows",{{"Id", Text.Trim, type text}}),
#"Merged Columns" = Table.CombineColumns(#"Trimmed Text",mergeHeaders[Headers],Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
in
#"Merged Columns"
You didn't load mergeHeaders in Power Query. Also, heading is headers not Headers. Hence use mergeHeaders[headers] not mergeHeaders[Headers].
Very new to Power Query and I did not understand that I had to load mergeHeaders into Power Query for this to work. Thank you for all your assistance I am appreciative
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
8 | |
6 | |
6 | |
6 | |
5 |