Forum Discussion

Insert_Key's avatar
Insert_Key
Brass Contributor
Oct 11, 2024
Solved

Dynamically select and merge columns while retaining others

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

BI & Data Analysis
excel
office 365
  • Lorenzo's avatar
    Lorenzo
    Oct 11, 2024

    Hi Insert_Key 

     

    The problem comes from:

    Select Label Columns = Table.SelectColumns(Source, #"List Label Columns")

    You don't need to do that. Instead, simply pass the list of columns to merge (List Label Columns) to the step where you merge the columns - line 8 below. Something like:

    let
    Source = ...,
    SourceNames = Table.ColumnNames( Source ),
    ListLabelColumns = List.Select( SourceNames, each
        Text.StartsWith(_, "Labels")
    ),
    MergedLabelColumns = Table.CombineColumns( Source,
        ListLabelColumns,
        Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
        "MERGED LABELS"
    ),
    Result = if List.Count(ListLabelColumns) < 2
             then Source
             else MergedLabelColumns
in
    Result

     

7 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 22, 2024

    Insert_Key Sorry this didn't help you

    (and next time a similar issue arises, someone will have to reinvent the wheel - thank you)

    • Insert_Key's avatar
      Insert_Key
      Brass Contributor
      Oct 27, 2024

      That's not it all, Lorenzo.

      I'm sorry if my lack of response seemed like I didn't appreciate your response. I was completely  snowed under at work with multiple competing priorities, and couldn't get your solution to fully resolve in my real-world file - presumably due to a skills shortage on my end. I was embarrassed to ask for more help, and to be honest, didn't have the time to. I posted on the 11th, you replied on the 11th, and have been on two weeks of Annual Leave since. I return to work tomorrow, and couldn't help but peek at my Inbox while away - there's a month's worth of work queued up for me already.

      I am sure that your solution would work - but as I couldn't get it to with my actual data (and didn't have time to test in a dummy environment) I didn't want to check the "Mark as best response" button. As soon as I have been able to make it work - either in  test or in prod - I will definitely return to click that button. I understand why that is so important (especially in a community setting) and genuinely appreciate your response to my call for help.

      Thanks again 🙂 and apologies again 🙁

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Oct 27, 2024

        Fair enough Insert_Key If you have challenge implementing, share a dummy representative workbook (PQ isn't easy when beginning)

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 11, 2024

    Hi Insert_Key 

     

    The problem comes from:

    Select Label Columns = Table.SelectColumns(Source, #"List Label Columns")

    You don't need to do that. Instead, simply pass the list of columns to merge (List Label Columns) to the step where you merge the columns - line 8 below. Something like:

    let
    Source = ...,
    SourceNames = Table.ColumnNames( Source ),
    ListLabelColumns = List.Select( SourceNames, each
        Text.StartsWith(_, "Labels")
    ),
    MergedLabelColumns = Table.CombineColumns( Source,
        ListLabelColumns,
        Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
        "MERGED LABELS"
    ),
    Result = if List.Count(ListLabelColumns) < 2
             then Source
             else MergedLabelColumns
in
    Result

     

Resources