Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ryan-W
New Member

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:LK1351564Item Description:Stuff   
Qty Back OrderedQty On OrderQty RequisitionedQty On HandQty AllocatedCurrent CostInventory Value
0304803.580.5
Item Number:5616-513Item Description:5616-513 Stuff   
Qty Back OrderedQty On OrderQty RequisitionedQty On HandQty AllocatedCurrent CostInventory Value
0006546500.2456300
Item Number:165463Item Description:More Stuff   
Qty Back OrderedQty On OrderQty RequisitionedQty On HandQty AllocatedCurrent CostInventory Value
000122261.5500
Item Number:45642Item Description:Lots O Stuff   
Qty Back OrderedQty On OrderQty RequisitionedQty On HandQty AllocatedCurrent CostInventory Value
20030200.456500

 

What I would want the output to be:

Item NumberItem DescriptionQty Back OrderedQty On OrderQty RequisitionedQty On HandQty AllocatedCurrent CostInventory Value
LK1351564Stuff0304803.580.5
5616-5135616-513 Stuff0006546500.2456300
165463More Stuff000122261.5500
45642Lots O Stuff20030200.456500

 

I am pretty new to power query, any help would be greatly appreciated.

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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 

View solution in original post

1 REPLY 1
slorin
Super User
Super User

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 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors