dynamically merge columns without null in power query
Hello! In power query, I want to merge specific columns that start with "cdp" Some columns are null values which I do not want in my final output. For example, I have 4 columns "cdp1" "cdp2" "cdp3" "cdp4" that should be merged into one column called "merge" skipping values that are null and separating each value by a semicolon. I need the code to work when there are varying amounts of columns to be merged like 3, 8, 18 columns need to be merged.
My current code does this, but the output is in a table format. However I need the output as text. How do I change the output from a table to text? Or 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 in power query to be "fish;cat" instead of the Table format.
My code:
let
Source = #"Replaced Value 18", (previous step)
LabelColumns = List.Select(Table.ColumnNames(S ource), each Text.StartsWith L, "cdp")),
AddMergedLabels = Table.AddColumn(Source, "merge", each Text.Combine(List.RemoveNulls(Li st.Transform (LabelColumns, (col) = Record.Field (_, col)), "."), type text)
in
AddMergedLabels
This is what my code outputs, multiple tables, but I need the output to be the text that is in the tables.
[link] [comments]
Want to read more?
Check out the full article on the original site