Forum Discussion

Ryan-W's avatar
Ryan-W
New Member
3 years ago
Solved

Generating a single line for each item based on cross tabulated dataset

I want to generate a table in excel from a report I pull from our ERP system. I want that table to have all the data for each item number on a single row with the column headers being the nomenclatur...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    Hello

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZFNa8MwDIb/ism5Df6m7LZ1h461Ddtgl9BDlroQltqtYw/67ye5ySijuQcsS68kbD2oLLOXYI5kG49fxj9ks2z9yoRiSkuIU+nZdLVvTqFxFusfIR4O4Puzm5XZW7iQp6r+JoXfG2/2kMdUYa+JXr6bc2y6Bt+5bVlVdlCPbevqKqTqMnpvbCBL1wWcxP6Acv5CPqs2mvQthbwAQy8XfSByBfeCgsOef3BKMz1XTIywDWUyLcjBtJIap0w651KhEpTeI2XYPMa5cd5MlJFxDjdHMpZWqe7zAbzkI3hrFzpSTAiQ3wAK+qfy6wYT4e4X", BinaryEncoding.Base64), Compression.Deflate)),
    let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t, #"Column 6" = _t, #"Column 7" = _t]),

    Columns = {Source[Column 1]{0},Source[Column 3]{0}}&Record.ToList(Source{1}),
    Rows = Table.Group(
    Source,
    {"Column 1"},
    {{"Rows", each {_[Column 2]{0},_[Column 4]{0}}&Record.ToList(_{2})}},
    GroupKind.Local,
    (x,y) => if y[Column 1]="Item Number:" then 1 else 0
    )
    in
    #table(Columns,Rows[Rows])

     

    Stéphane 

slorin's avatar
slorin
Super User
3 years ago

Hello

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZFNa8MwDIb/ism5Df6m7LZ1h461Ddtgl9BDlroQltqtYw/67ye5ySijuQcsS68kbD2oLLOXYI5kG49fxj9ks2z9yoRiSkuIU+nZdLVvTqFxFusfIR4O4Puzm5XZW7iQp6r+JoXfG2/2kMdUYa+JXr6bc2y6Bt+5bVlVdlCPbevqKqTqMnpvbCBL1wWcxP6Acv5CPqs2mvQthbwAQy8XfSByBfeCgsOef3BKMz1XTIywDWUyLcjBtJIap0w651KhEpTeI2XYPMa5cd5MlJFxDjdHMpZWqe7zAbzkI3hrFzpSTAiQ3wAK+qfy6wYT4e4X", BinaryEncoding.Base64), Compression.Deflate)),
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t, #"Column 6" = _t, #"Column 7" = _t]),

Columns = {Source[Column 1]{0},Source[Column 3]{0}}&Record.ToList(Source{1}),
Rows = Table.Group(
Source,
{"Column 1"},
{{"Rows", each {_[Column 2]{0},_[Column 4]{0}}&Record.ToList(_{2})}},
GroupKind.Local,
(x,y) => if y[Column 1]="Item Number:" then 1 else 0
)
in
#table(Columns,Rows[Rows])

 

Stéphane