Forum Discussion

rshh's avatar
rshh
New Member
2 years ago
Solved

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. ...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    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's avatar
jballGT
New Member
1 year ago

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-IgnoreHeaderColumn2-IgnoreHeaderColumn3-IgnoreHeaderColumn4-IgnoreHeaderIDColorWeightLength
Column1-IgnoreContent1Column2-IgnoreContent1Column3-IgnoreContent1Column4-IgnoreContent1P001blue50100
Column1-IgnoreContent2Column2-IgnoreContent2Column3-IgnoreContent2Column4-IgnoreContent2P003red4090

 

then the results would be:

Column1-IgnoreHeaderColumn2-IgnoreHeaderColumn3-IgnoreHeaderColumn4-IgnoreHeaderIDColorWeightLengthProductInformation
Column1-IgnoreContent1Column2-IgnoreContent1Column3-IgnoreContent1Column4-IgnoreContent1P001blue50100ID: P001, Color: blue, Weight: 50, Length: 100
Column1-IgnoreContent2Column2-IgnoreContent2Column3-IgnoreContent2Column4-IgnoreContent2P003red4090ID: P003, Color: red, Weight: 40, Length: 90
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

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