Partial Transform

[mrmdsims]

I have a table of orders where the items in the order are all on one line. I need to have each item on its own line along with the order number and date. Some orders could have up to 253 items but of course, I do not want to transform the ones that are blank. Power Query got me very close but I could not change Item, Description, and Quantity to a column and have the values below.

Any help would be appreciated.

Mike

 

[sandy666]

I suggest to post your example data with XL2BB, nobody want to retype your data from the picture

 

[mrmdsims]

Here is the spreadsheet. Thanks Sandy666

Order Info.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1	From This
2	Order	Date	Item -1	Item - 1 Description	Item - 1 Quantity	Item -2	Item -2 Decription	Item -2 Quantity	Item -3	Item -3 Description	Item -3 Quantity
3	123456	4/8/2020	ABC	ABC Thing	1	DEF	DEF Thing	3	XYZ	XYZ Thing	200
4	456789	4/8/2020	ABC	ABC Thing	4
5	123789	4/8/2020	DEF	DEF Thing	3	XYZ	XYZ Thing	2
6
7	To This
8	Order	Date	Item	Description	Quantity
9	123456	4/8/2020	ABC	ABC Thing	1
10	123456	4/8/2020	DEF	DEF Thing	3
11	123456	4/8/2020	XYZ	XYZ Thing	200
12	456789	4/8/2020	ABC	ABC Thing	4
13	123789	4/8/2020	DEF	DEF Thing	3
14	123789	4/8/2020	XYZ	XYZ Thing	2
Sheet2

 

[sandy666]

Order	Date	Item -1	Item - 1 Description	Item - 1 Quantity	Item -2	Item -2 Description	Item -2 Quantity	Item -3	Item -3 Description	Item -3 Quantity		Order	Date	Item	Description	Quantity
123456	09/04/2020	ABC	ABC Thing	1	DEF	DEF Thing	3	XYZ	XYZ Thing	200		123456	09/04/2020	ABC	ABC Thing	1
456789	09/04/2020	ABC	ABC Thing	4								123456	09/04/2020	DEF	DEF Thing	3
123789	09/04/2020	DEF	DEF Thing	3	XYZ	XYZ Thing	2					123456	09/04/2020	XYZ	XYZ Thing	200
												456789	09/04/2020	ABC	ABC Thing	4
												123789	09/04/2020	DEF	DEF Thing	3
												123789	09/04/2020	XYZ	XYZ Thing	2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Order", "Date"}, "Attribute", "Value"),
    Integer = Table.TransformColumns(Table.AddIndexColumn(UOC, "Index", 0, 1), {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    List = Table.AddColumn(Table.Group(Integer, {"Order", "Date", "Index"}, {{"Count", each _, type table}}), "Custom", each List.Distinct([Count][Value])),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    Rename = Table.RenameColumns(Split,{{"Custom.1", "Item"}, {"Custom.2", "Description"}, {"Custom.3", "Quantity"}}),
    Type = Table.TransformColumnTypes(Table.SelectColumns(Rename,{"Order", "Date", "Item", "Description", "Quantity"}),{{"Date", type date}})
in
    Type

 

[mrmdsims]

Perfect. Thank you very much.

 

[sandy666]

You are welcome
Thanks for the feedback