Combining header names and values for all rows into new column

[rshh]

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é

[AlienSx · Accepted solution]

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

[jballGT]

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]

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

[rshh]

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). 

[rshh]

Thanks for the clarification and the code update!