Power Query transformation

[paulohk]

Hello, 

the following data's are extract from a software and is presented as follow :

Column 1	Column 2	Column 3	Column 4	Lot number	Piece
TEST NAME	TEST A	TEST B	TEST C	A00005	1
SITE01	4	6	0.2	A00005	1
SITE02	4	6	0.3	A00005	1
TEST NAME	TEST A	TEST B	TEST C	A00005	2
SITE01	2	5	0.3	A00005	2
SITE02	2	6	0.3	A00005	2

With power query, I would like to transport the table to get this, and have the test name in a column  : 

SITE	TEST NAME	VALUE	Lot number	Piece
SITE01	TEST A	4	A00005	1
SITE01	TEST B	6	A00005	1
SITE01	TEST C	0.2	A00005	1
SITE02	TEST A	4	A00005	1
SITE02	TEST B	6	A00005	1
SITE02	TEST C	0.3	A00005	1
SITE01	TEST A	2	A00005	2
SITE01	TEST B	5	A00005	2
SITE01	TEST C	0.3	A00005	2
SITE02	TEST A	2	A00005	2
SITE02	TEST B	6	A00005	2
SITE02	TEST C	0.3	A00005	2

I supposed it could be usefull to use unpivot function, but i didn't managed to get the good result. Do you have an idea ?

Thanks for your help !

Have a good day.
 

[dufoq3 · Accepted solution]

Hi @paulohk, check this:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnENDlHwc/R1VdKBsB1hDCcYwxnIcDQAAlMgw1ApVidaKdgzxNXAUAEGgOImQGwGxAZ6RjiUG2FXboypnFQ3GeFwE8glppiWGOFwkxF2NwGVxwIA", 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, #" Lot number" = _t, Piece = _t]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(PromotedHeaders, {"TEST NAME", "A00005", "1"}, "Attribute", "Value"),
    RenamedColumns = Table.RenameColumns(UnpivotedOtherColumns,{{"TEST NAME", "SITE"}, {"Attribute", "TEST NAME"}, {"A00005", "Lot number"}, {"Value", "VALUE"}, {"1", "Piece"}}),
    FilteredRows = Table.SelectRows(RenamedColumns, each ([SITE] <> "TEST NAME")),
    ReorderedColumns = Table.ReorderColumns(FilteredRows,{"SITE", "TEST NAME", "VALUE", "Lot number", "Piece"})
in
    ReorderedColumns

[Hoki_34789]

Saya sebagai siswa akuntansi ingin sekali belajar power bi, minta saranya?