Forum Discussion
Combining header names and values for all rows into new column
hello, rshh
let Source = your_table, cols = List.Buffer(Table.ColumnNames(Source)), p_info = Table.AddColumn( Source, "ProductInformation", (x) => [a = List.Zip({cols, Record.FieldValues(x)}), b = List.Select(a, (w) => w{1} <> null), c = List.Transform(b, (w) => w{0} & ": " & Text.From(w{1})), d = Text.Combine(c, ", ")][d] ) in p_info
Wow, AlienSx, thanks a lot for your quick help! Works almost perfectly. I only had to modify one line:
b = List.Select(a, (w) => w{1} <> null and w{1} <> ""),because my empty cells do not contain null.
I am quite new to Power Query M and still have trouble to understand the syntax, but I want to learn. As far as I understand you do the following:
Create a paired list (zip) of the column names and the row
Create a reduced list without empty values
Write column names and values into a single field using ': ' as separator
Combine these fields using ', ' as separator
Create a new column "Productinformation" holding the combined fields
Right?
Thanks and best regards,
René
Super UserWrite column names and values into a single field using ': ' as separator
rshh we still have a list at this point. This line transforms this list from list of pairs to the list of single (text) values. Next step combines these values into a text string. Other than this you are right.
I modified my original code a little bit. Now I simply add new column with the same set of transformations (was Table.ToList >> transformations >> Table.FromList).
Thanks for the clarification and the code update!