Forum Discussion

vc25's avatar
vc25
Helper I
6 months ago
Solved

Dynamically merge specific columns without null and output as text

Hello, in power query, I want to merge specific columns that start with "cdp" Some columns are null values which I do not want to show up in my final output. For example, I have 4 columns "cdp1" "cdp...
Need Help
  • techies's avatar
    techies
    6 months ago

    Hi vc25 pls remove the highlighted lines

     

     

    Since Changed Type 3 already converts fish columns to text, you can just add the merge step after that and it will show values instead of table.

     

    AddMergedLabels =
        Table.AddColumn(
            #"Changed Type3",
            "merge",
            each
                Text.Combine(
                    List.RemoveNulls(
                        List.Transform(
                            List.Select(
                                Table.ColumnNames(#"Changed Type3"),
                                each Text.StartsWith(_, "fish")
                            ),
                            (c) => Record.Field(_, c)
                        )
                    ),
                    ";"
                ),
            type text
        )
in
    AddMergedLabels
vc25's avatar
vc25
Helper I
6 months ago

Hello, this did not work. I am still getting a table as my output. The table output is outputting all of my columns including columns not starting with "cdp". I only want the last column of the table which is the column that merges the "cdp" columns. 

techies's avatar
techies
Super User
6 months ago

Hi vc25 here is the output on my end

 

 

 

 

  • vc25's avatar
    vc25
    Helper I
    6 months ago

    this is what I get. 

     

    if i click on the table in the Custom colum, then I get the values, but is there a way I can do it without clicking on the "table" link in the merge column

     

     

    = Table.AddColumn(#"Changed Type3", "Custom", each let
    Source = #"Changed Type3",

    LabelColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "fish")),

    AddMergedLabels =
    Table.AddColumn(
    Source,
    "merge",
    each
    Text.Combine(
    List.RemoveNulls(
    Record.ToList(
    Record.SelectFields(
    _,
    List.Select(
    Record.FieldNames(_),
    each Text.StartsWith(_, "fish")
    )
    )
    )
    ),
    ";"
    ),
    type text
    )

    in
    AddMergedLabels)

    • techies's avatar
      techies
      Super User
      6 months ago

      Hi vc25 have converted the fish columns to text first and then merged them. This shows the result directly, otherwise power query returns a table per row. Here is the code :

       

       

       // Convert fish columns to text
    #"Changed Type" =
        Table.TransformColumns(
            Source,
            List.Transform(
                List.Select(
                    Table.ColumnNames(Source),
                    each Text.StartsWith(_, "fish")
                ),
                each {_, each if _ = null then null else Text.From(_), type text}
            )
        ),

    // Merge fish* columns into text
    AddMergedLabels =
        Table.AddColumn(
            #"Changed Type",
            "merge",
            each
                Text.Combine(
                    List.RemoveNulls(
                        List.Transform(
                            List.Select(
                                Table.ColumnNames(#"Changed Type"),
                                each Text.StartsWith(_, "fish")
                            ),
                            (c) => Record.Field(_, c)
                        )
                    ),
                    ";"
                ),
            type text
        )
in
    AddMergedLabels

       

       

       

      • vc25's avatar
        vc25
        Helper I
        6 months ago

        Yeah, I changed the data type to text first before merging. I keep getting this error.