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 t...
  • Lorenzo's avatar
    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