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

[Anonymous]

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

[rohit_singh]

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]

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

[Vijay_A_Verma · Accepted solution]

Use this

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

[Anonymous]

Hello Vijay,

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

even though the Table has the proper name

[Vijay_A_Verma]

I want to see your complete query. 

[Anonymous]

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"

[Vijay_A_Verma]

You didn't load mergeHeaders in Power Query. Also, heading is headers not Headers. Hence use mergeHeaders[headers] not mergeHeaders[Headers].

[Anonymous]

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