Forum Discussion
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 nomenclature of the item, item number, amount on hand, on order and so on. The report generates each item and item nomenclature on a single row and the the amount on hand, on order, and other information on the row below with the data for that information in the same column on the row below that. Each item currently takes up three rows within the report. I can generate the report into an excel document and I would like to finish with a table in excel.
Input from report, this continues on for several hundred items:
| Item Number: | LK1351564 | Item Description: | Stuff | |||
| Qty Back Ordered | Qty On Order | Qty Requisitioned | Qty On Hand | Qty Allocated | Current Cost | Inventory Value |
| 0 | 3 | 0 | 48 | 0 | 3.5 | 80.5 |
| Item Number: | 5616-513 | Item Description: | 5616-513 Stuff | |||
| Qty Back Ordered | Qty On Order | Qty Requisitioned | Qty On Hand | Qty Allocated | Current Cost | Inventory Value |
| 0 | 0 | 0 | 6546 | 50 | 0.2456 | 300 |
| Item Number: | 165463 | Item Description: | More Stuff | |||
| Qty Back Ordered | Qty On Order | Qty Requisitioned | Qty On Hand | Qty Allocated | Current Cost | Inventory Value |
| 0 | 0 | 0 | 122 | 26 | 1.5 | 500 |
| Item Number: | 45642 | Item Description: | Lots O Stuff | |||
| Qty Back Ordered | Qty On Order | Qty Requisitioned | Qty On Hand | Qty Allocated | Current Cost | Inventory Value |
| 2 | 0 | 0 | 302 | 0 | 0.456 | 500 |
What I would want the output to be:
| Item Number | Item Description | Qty Back Ordered | Qty On Order | Qty Requisitioned | Qty On Hand | Qty Allocated | Current Cost | Inventory Value |
| LK1351564 | Stuff | 0 | 3 | 0 | 48 | 0 | 3.5 | 80.5 |
| 5616-513 | 5616-513 Stuff | 0 | 0 | 0 | 6546 | 50 | 0.2456 | 300 |
| 165463 | More Stuff | 0 | 0 | 0 | 122 | 26 | 1.5 | 500 |
| 45642 | Lots O Stuff | 2 | 0 | 0 | 302 | 0 | 0.456 | 500 |
I am pretty new to power query, any help would be greatly appreciated.
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
1 Reply
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