Need help with power query unpivoting.

[proficient]

Hello all,

Please check out the below screenshot of the normal table (there is an unnecessary break in each machine table) that I am getting from my colleague "DATA" along with the "DESIRED RESULT" that I want as the final result.

The data is in a regular excel table form and it would be time-taking to make official tables of each machine (a total of 16 machines) every time. I am looking forward to a code that will work if I convert the complete data into one sheet to save time by not making each machine an official excel table every time, Could anyone provide a code?

 

[alansidman]

Cannot manipulate data in a picture. Please upload your source data employing XL2BB so we don't have to guess and recreate the structure of your data.

 

[proficient]

Cannot manipulate data in a picture. Please upload your source data employing XL2BB so we don't have to guess and recreate the structure of your data.

There you go!

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		DATA
2															DESIRED RESULT
3		MACHINE 1				MACHINE 2				MACHINE 3					MACHINE #	DATE	ARTICLE	OUTPUT
4		DATE	ARTICLE	OUTPUT		DATE	ARTICLE	OUTPUT		DATE	ARTICLE	OUTPUT			1	1-Oct	ABC	300
5		1-Oct	ABC	300		1-Oct	BCD	300		1-Oct	EFG	300			1	2-Oct	ABC	500
6		2-Oct	ABC	500		2-Oct	BCD	500		2-Oct	EFG	500			1	3-Oct	ABC	500
7		3-Oct	ABC	500		3-Oct	BCD	500		3-Oct	EFG	500			1	4-Oct	HIG	500
8															1	5-Oct	PLK	250
9		DATE	ARTICLE	OUTPUT		DATE	ARTICLE	OUTPUT		DATE	ARTICLE	OUTPUT			1	6-Oct	PLK	350
10		4-Oct	HIG	500		4-Oct	KLM	500		4-Oct	PQR	150			2	1-Oct	BCD	300
11		5-Oct	PLK	250		5-Oct	KLM	500		5-Oct	PQR	250			2	2-Oct	BCD	500
12		6-Oct	PLK	350		6-Oct	KLM	500		6-Oct	PQR	500			2	3-Oct	BCD	500
13															2	4-Oct	KLM	500
14															2	5-Oct	KLM	500
15															2	6-Oct	KLM	500
16															3	1-Oct	EFG	300
17															3	2-Oct	EFG	500
18															3	3-Oct	EFG	500
19															3	4-Oct	PQR	150
20															3	5-Oct	PQR	250
21															3	6-Oct	PQR	500
22
Sheet2

Cell Formulas
Range	Formula
B6:B7,J11:J12,F11:F12,B11:B12,J6:J7,F6:F7	B6	=B5+1

 

[alansidman]

I used power query to achieve your expected result.
1. I loaded each machine table to Power Query and for each ran this query changing the table name for each.

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([DATE] <> null and [DATE] <> "DATE")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"DATE", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Machine", each 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Machine", "DATE", "ARTICLE", "OUTPUT"})
in
    #"Reordered Columns"

2. I then appended each to the other

let
    Source = Table.Combine({Table5, Table6, Table7})
in
    Source

My end result replicated your expected results

 

[proficient]

I used power query to achieve your expected result.
1. I loaded each machine table to Power Query and for each ran this query changing the table name for each.

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([DATE] <> null and [DATE] <> "DATE")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"DATE", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Machine", each 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Machine", "DATE", "ARTICLE", "OUTPUT"})
in
    #"Reordered Columns"

2. I then appended each to the other

let
    Source = Table.Combine({Table5, Table6, Table7})
in
    Source

My end result replicated your expected results

Hello Alansidman,

Thank you for your efforts but the provided data is sample data, there are more than 15 machines, and I cannot make them table each time, which is why I asked if is there a way to make the complete data a table and then convert it into one data?


I am new to power query but can make one data from multiple sheet, but this is something challenging to me.

 

[adre]

Name machine data tables in some systematic way that you can loop over (e.g. Machine-1, Machine-2, ...) Have a cell in your spreadsheet for the number of data tables.
You should then be able to write M language code to loop over all the tables and grab their data into a single query.

 

[lrobbo314]

Here is a way that you can combine all of the data without needing to define each machine as its own table.

I selected all of the sample data, loaded into PQ, then applied the following steps which you should be able to just paste into the PQ advanced editor.

PROFICIENT
	B	C	D	E	F	G	H	I	J	K	L
1	DATA
2
3
4	MACHINE 1				MACHINE 2				MACHINE 3
5	DATE	ARTICLE	OUTPUT		DATE	ARTICLE	OUTPUT		DATE	ARTICLE	OUTPUT
6	44835	ABC	300		44835	BCD	300		44835	EFG	300
7	44836	ABC	500		44836	BCD	500		44836	EFG	500
8	44837	ABC	500		44837	BCD	500		44837	EFG	500
9
10	DATE	ARTICLE	OUTPUT		DATE	ARTICLE	OUTPUT		DATE	ARTICLE	OUTPUT
11	44838	HIG	500		44838	KLM	500		44838	PQR	150
12	44839	PLK	250		44839	KLM	500		44839	PQR	250
13	44840	PLK	350		44840	KLM	500		44840	PQR	500
14
15	PQ Output
16	Machine	DATE	ARTICLE	OUTPUT
17	MACHINE 1	10/1/2022	ABC	300
18	MACHINE 1	10/2/2022	ABC	500
19	MACHINE 1	10/3/2022	ABC	500
20	MACHINE 1	10/4/2022	HIG	500
21	MACHINE 1	10/5/2022	PLK	250
22	MACHINE 1	10/6/2022	PLK	350
23	MACHINE 2	10/1/2022	BCD	300
24	MACHINE 2	10/2/2022	BCD	500
25	MACHINE 2	10/3/2022	BCD	500
26	MACHINE 2	10/4/2022	KLM	500
27	MACHINE 2	10/5/2022	KLM	500
28	MACHINE 2	10/6/2022	KLM	500
29	MACHINE 3	10/1/2022	EFG	300
30	MACHINE 3	10/2/2022	EFG	500
31	MACHINE 3	10/3/2022	EFG	500
32	MACHINE 3	10/4/2022	PQR	150
33	MACHINE 3	10/5/2022	PQR	250
34	MACHINE 3	10/6/2022	PQR	500
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Flip0 = Table.Transpose(Source),
    RemoveBlanks0 = Table.SelectRows(Flip0, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    Flip1 = Table.Transpose(RemoveBlanks0),
    RemoveBlanks1 = Table.Skip(Table.Distinct(Table.Skip(Table.SelectRows(Flip1, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),1),"Column1"),1),
    Machines = List.RemoveNulls(Record.ToList(Source{0})),
    Cols = List.Distinct(List.RemoveNulls(Record.ToList(Source{1}))),
    ToTables = List.Transform(List.Split(Table.ToColumns(RemoveBlanks1),List.Count(Cols)), each Table.FromColumns(_,Cols)),
    Combine = Table.FromColumns({Machines} & {ToTables},{"Machine","C2"}),
    Expand = Table.TransformColumnTypes(Table.ExpandTableColumn(Combine, "C2", Cols, Cols),{{"Machine", type text}, {"ARTICLE", type text}, {"DATE", type date}, {"OUTPUT", Int64.Type}})
in
    Expand

 

[lrobbo314]

Here's an updated and a little more streamlined version that avoids transposing the table multiple times.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemoveEmptyCols = Table.SelectColumns(Source,List.Select(Table.ColumnNames(Source), each List.MatchesAny(Table.Column(Source,_), each _ <> null))),
    RemoveEmptyRows = Table.SelectRows(RemoveEmptyCols, each _[Column1] <> null),
    Machines = List.RemoveNulls(Record.ToList(RemoveEmptyRows{0})),
    Cols = List.Distinct(Record.ToList(RemoveEmptyRows{1})),
    ToTables = List.Transform(List.Split(Table.ToColumns(Table.Skip(Table.SelectRows(RemoveEmptyRows, each _[Column1] <> "DATE"),2)),List.Count(Cols)), each Table.FromColumns(_,Cols)),
    Combine = Table.FromColumns({Machines} & {ToTables},{"Machine","C2"}),
    Expand = Table.ExpandTableColumn(Combine, "C2", Cols, Cols)
in
    Expand

 

[proficient]

Here's an updated and a little more streamlined version that avoids transposing the table multiple times.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemoveEmptyCols = Table.SelectColumns(Source,List.Select(Table.ColumnNames(Source), each List.MatchesAny(Table.Column(Source,_), each _ <> null))),
    RemoveEmptyRows = Table.SelectRows(RemoveEmptyCols, each _[Column1] <> null),
    Machines = List.RemoveNulls(Record.ToList(RemoveEmptyRows{0})),
    Cols = List.Distinct(Record.ToList(RemoveEmptyRows{1})),
    ToTables = List.Transform(List.Split(Table.ToColumns(Table.Skip(Table.SelectRows(RemoveEmptyRows, each _[Column1] <> "DATE"),2)),List.Count(Cols)), each Table.FromColumns(_,Cols)),
    Combine = Table.FromColumns({Machines} & {ToTables},{"Machine","C2"}),
    Expand = Table.ExpandTableColumn(Combine, "C2", Cols, Cols)
in
    Expand

Hello Irobbo314: Could you please share the file I am getting errors.

 

[Automatrix]

How do the data make it into the table? Does your colleague by any chance collect it from different files? That would be a good time to import from folder.