Forum Discussion
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" "cdp2" "cdp3" "cdp4" that are merged into one column called "merge" skipping values that are null and separating each value by a semicolon. My current code does this, but the output is in a table format. However, I need the output as a text. How do I change the output from a table to text? The table contains one column called merge. For example, if I click on the first table, the value is "fish;cat" I would like the output to be "fish;cat" instead of Table format.
My code:
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))), ";"), type text)
in
AddMergedLabels
This is what my code outputs. The output is a table
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
17 Replies
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 AddMergedLabelsPlease 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!
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.
Could you try the code below:
let Source = #"Replaced Value18", LabelColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "cdp")), AddMergedLabels = Table.AddColumn( Source, "merge_text", (r) => Text.Combine( List.RemoveNulls( List.Transform(LabelColumns, (c) => let v = Record.Field(r, c) in if v = null then null else Text.From(v) ) ), ";" ), type text ) in AddMergedLabelsHello, 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.
AddMergedLabels = Table.AddColumn( Source, "merge", each Text.Combine( List.RemoveNulls( Record.FieldValues( Record.SelectFields(_, LabelColumns) ) ), ";" ), type text )your output is table because you are using PQ Editor UI to add new column and paste this code into to the box.
