Forum Discussion
Combining header names and values for all rows into new column
Hi folks,
I am new in this community and I hope someone can help me with this slightly unusual problem. I did a lot of web research without success, but maybe I did not ask the right questions.
I got a table with product data and have to combine it into a new column that can be fed into an LLM to generate a product description based on that information. Let's say my table looks like this:
| ID | Color | Weight | Length |
| P001 | blue | 50 | 100 |
| P003 | red | 40 | 90 |
Then I need to create a new column containing the column names and the corresponding values for each row.
| ID | Color | Weight | Length | ProductInformation |
| P001 | blue | 50 | 100 | ID: P001, Color: blue, Weight: 50, Length: 100 |
| P003 | red | 40 | 90 | ID: P003, Color: red, Weight: 40, Length: 90 |
Of course, I do not want to set up the statement to create the new column manually, since in reality I have over 40 columns of product data and that number might change in the future. If possible, columns with missing data should be skipped instead of creating an meaningless entry like "Color:".
Any tips and comments if and how this be done with Power Query M would be greatly appreciated.
Thanks and best regards,
René
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
6 Replies
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_infoWow, 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é
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).
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 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
