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
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
I keep getting ')' error.
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 AddMergedLabelsI keep getting an error "token Eof expected."