Hi there

I am developing a solution in Power Query around a system export that contains a number of columns commencing with the text “Labels”. The number of these columns will vary depending on the query that generates the export, and I would like to build in some flexibility/intelligence so that Power Query will pick up all columns starting with “Labels” and merge them. The export’s header syntax is: Labels, Labels1, Labels2 and so on.

The original steps that I used are:

Inserted Merged Column = Table.AddColumn(#”Select Label Columns”, “Merged Labels”, each Text.Combine({[Labels], [Labels2], [Labels3], [Labels4]}, ” | “), type text)
Removed Columns = Table.RemoveColumns(#”Inserted Merged Column”,{“Parent id”, “Custom field (Access Type)”, “Issue id”, “Labels”, “Labels2”, “Labels3”, “Labels4”})

which would be fine if the number of Labels columns was fixed at four. I have added a new merged column and deleted the originals as when I did a direct merge, the delimiter was being added to empty cells – creating entries like ” | | | ” and this was a quick workaround.

In an attempt to dynamically identify and merge the columns for each data refresh, I inserted two steps after initial Source code:

Source = Excel.CurrentWorkbook(){[Name=”Export_Data”]}[Content]
List Label Columns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,”Labels”))
Select Label Columns = Table.SelectColumns(Source, #”List Label Columns”)

This correctly selects the Labels columns, but it also removes all of the other columns in the table that aren’t in the List but are required for the report. I’ve tried, unsuccessfully, to combine the List functions into the merge steps and am at a loss.

How can I dynamically identify these “Labels” columns, merge them, and retain all other columns? I’m hoping that the solution will be obvious to you gurus out there, but if it helps, I can definitely create a dummy file. Looking forward to your help – thanks in advance. 🤗

​Hi there I am developing a solution in Power Query around a system export that contains a number of columns commencing with the text “Labels”. The number of these columns will vary depending on the query that generates the export, and I would like to build in some flexibility/intelligence so that Power Query will pick up all columns starting with “Labels” and merge them. The export’s header syntax is: Labels, Labels1, Labels2 and so on. The original steps that I used are:Inserted Merged Column = Table.AddColumn(#”Select Label Columns”, “Merged Labels”, each Text.Combine({[Labels], [Labels2], [Labels3], [Labels4]}, ” | “), type text)
Removed Columns = Table.RemoveColumns(#”Inserted Merged Column”,{“Parent id”, “Custom field (Access Type)”, “Issue id”, “Labels”, “Labels2”, “Labels3”, “Labels4″}) which would be fine if the number of Labels columns was fixed at four. I have added a new merged column and deleted the originals as when I did a direct merge, the delimiter was being added to empty cells – creating entries like ” | | | ” and this was a quick workaround. In an attempt to dynamically identify and merge the columns for each data refresh, I inserted two steps after initial Source code:Source = Excel.CurrentWorkbook(){[Name=”Export_Data”]}[Content]
List Label Columns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,”Labels”))
Select Label Columns = Table.SelectColumns(Source, #”List Label Columns”)This correctly selects the Labels columns, but it also removes all of the other columns in the table that aren’t in the List but are required for the report. I’ve tried, unsuccessfully, to combine the List functions into the merge steps and am at a loss. How can I dynamically identify these “Labels” columns, merge them, and retain all other columns? I’m hoping that the solution will be obvious to you gurus out there, but if it helps, I can definitely create a dummy file. Looking forward to your help – thanks in advance. 🤗  Read More

​