Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Anonymous
Not applicable

Loop through a table of header names and merge all columns in Power Query with those headers

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

 

2022-05-24_09-31-29.jpg

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Use this

#"Merged Columns" = Table.CombineColumns(#"Trimmed Text",mergeHeaders[Headers],Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")

View solution in original post

8 REPLIES 8
rohit_singh
Solution Sage
Solution Sage

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! 😊

Anonymous
Not applicable

This is helpfe]ul in itself, thank you (but I could not see how to apply this to this question)

Vijay_A_Verma
Super User
Super User

Use this

#"Merged Columns" = Table.CombineColumns(#"Trimmed Text",mergeHeaders[Headers],Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
Anonymous
Not applicable

Hello Vijay,

I can not get it to work.  I get this error

2022-05-24_10-07-56.jpg

even though the Table has the proper name

2022-05-24_10-10-50.jpg

I want to see your complete query. 

Anonymous
Not applicable

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].

Anonymous
Not applicable

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.