Forum Discussion
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...
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
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.
