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
tharunkumarRTK's avatar
tharunkumarRTK
Super User
6 months ago

vc25 

I am assuming the table value in "merge" column contains a single column and single row, like shown in the below image

 

 I suggest you to use the below code to extract the text value from the table 

let
Source = #"Replaced Value18",

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

AddMergedLabels = Table.AddColumn(Source, "merge", each Text.Combine(List.RemoveNulls(List.Transform(LabelColumns, (col) => Record.Field(_, col))), ";"){0}[Column1], type text)

in
AddMergedLabels

Please note, you might need change  Column1 if the column name in the table value is not Column1

 

 

You can read my blogs here: techietips.co.in

 

 

Connect on LinkedIn

 

 

 








Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!
vc25's avatar
vc25
Helper I
6 months ago

Hello, Thank you for helping. Unfornately, the code did not work for me. The output was still a table and when I clicked on the table it errored. The error was this:

 Expression.Error: We cannot convert the value "fish.." to type List.
Details:
Value=fish
Type=[Type]

 

The data type for my cdp columns are text. With my original code, if I pressed on the table box, then the all text for the merge column would show, but I need it to automatically be the text instead of Table. Or do you have another suggestion to merge columns dynamically? I am merging columns after replacing values.