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
Super Userhello, 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é
- AlienSx
Write 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!
Hi, AlienSx I am attempting to use the code, but have a use case to build off of this. The columns that I'd like to use this with are only a portion of the entire table. For example using the example above, say there's a total of 8 columns in the table, and I only want to capture the columns 4-8 and not include ANY of the 1-4.
Column1-IgnoreHeader Column2-IgnoreHeader Column3-IgnoreHeader Column4-IgnoreHeader ID Color Weight Length Column1-IgnoreContent1 Column2-IgnoreContent1 Column3-IgnoreContent1 Column4-IgnoreContent1 P001 blue 50 100 Column1-IgnoreContent2 Column2-IgnoreContent2 Column3-IgnoreContent2 Column4-IgnoreContent2 P003 red 40 90 then the results would be:
Column1-IgnoreHeader Column2-IgnoreHeader Column3-IgnoreHeader Column4-IgnoreHeader ID Color Weight Length ProductInformation Column1-IgnoreContent1 Column2-IgnoreContent1 Column3-IgnoreContent1 Column4-IgnoreContent1 P001 blue 50 100 ID: P001, Color: blue, Weight: 50, Length: 100 Column1-IgnoreContent2 Column2-IgnoreContent2 Column3-IgnoreContent2 Column4-IgnoreContent2 P003 red 40 90 ID: P003, Color: red, Weight: 40, Length: 90 - dufoq3
Community Champion
Hi jballGT, check this:
You have to specify columns for Product Information:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM9T1TM/LL0p1zs8rSc0rMVTSgUoY4ZIwxiVhgikRYGAAopJySlOBlKkBkDA0MFCK1cFhvREu641wWW+Ey3ojiPXGQKooNQVImoBstwRaHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1-IgnoreHeader" = _t, #"Column2-IgnoreHeader" = _t, #"Column3-IgnoreHeader" = _t, #"Column4-IgnoreHeader" = _t, ID = _t, Color = _t, Weight = _t, Length = _t]), ProductInfoCols = {"ID", "Color", "Weight", "Length"}, Ad_ProductInformation = Table.AddColumn(Source, "Product Information", each Text.Combine(List.Transform(List.Intersect({Record.FieldNames(_), ProductInfoCols}), (x)=> Text.Combine({x, Record.Field(_, x)}, ": ")), ", "), type text) in Ad_ProductInformation
