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

[Ryan-W]

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.

[slorin · Accepted solution]

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