Unpivot

[ErikLe]

Hi,

I need some help with unpivot data in power query. At the moment this runs on SQL Server SSIS paket. In the future this will not be possible anymore. I hope, that you can help me.

My starting point is:

PK	Date	AG	ART	MA1	MA2	MA3	BA1	BA2	BA3	BS1	BS2	BS3	BZ1	BZ2	BZ3
1	01.01.2021	1	A1	M1	M2	M3	100	105	110	10	15	20	50	60	70
2	01.01.2021	1	A2	M2	M3		100	113		11	12	18	8	9
3	02.01.2021	2	A3	M3			100	2		3			2		6
4	02.01.2021	5	A3				12	13				8			8
5	03.01.2021	3	A4	M2	M3	M1	100	102	103	3	4	5	7	8	9
6	03.01.2021	1	A5	M3	M1		100	105	7	3	5		8	8

I tried serveral times to make it look like this:

PK	Date	AG	ART	MA	BA	BS	BZ
1	01.01.2021	1	A1	M1	100	10	50
1	01.01.2021	1	A1	M2	105	15	60
1	01.01.2021	1	A1	M3	110	20	70
2	01.01.2021	1	A2	M2	100	11	8
2	01.01.2021	1	A2	M3	113	12	9
2	01.01.2021	1	A2			18
3	02.01.2021	2	A3	M3	100	3	2
3	02.01.2021	2	A3		2
3	02.01.2021	2	A3				6
4	02.01.2021	5	A3		12
4	02.01.2021	5	A3		13
4	02.01.2021	5	A3			8	8
5	03.01.2021	3	A4	M2	100	3	7
5	03.01.2021	3	A4	M3	102	4	8
5	03.01.2021	3	A4	M1	103	5	9
6	03.01.2021	1	A5	M3	100	3	8
6	03.01.2021	1	A5	M1	105	5	8
6	03.01.2021	1	A5		7

MA "N",

BA "N",

BS "N"

and

BZ "N"
belong together under "N"

PK, Date, AG, Art are not touched

Each MA, BA, BS and BZ can be empty. Rows have to be shown, if MA || BA || BS || BZ are not empty. Only if all 4 are empty, the row can be removed.

Can anyone help me with this problem?

Greetings
Erik

[Anonymous]

try this. you have to complete the last step: that of deleting records with nulls

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBDoUgDESvYlibn9IC6pIDeALC/a/xnQpaiAlM2jI8Sktx3q2O/O9aTIwEO0NOFYYI6kSqEervGIKcEUVIgmzk6locf6J5oC4v2T+5emHx+yXYhx4BChOxgcKXxfJeJrfMnvRaUlyYcbHjFkvTZubqPsSg4TKJoeFKDsOPdax9mMolac7Q3t+eXwOaZqiOMQ48M0bqBGm03urdbq1/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PK = _t, Date = _t, AG = _t, ART = _t, MA1 = _t, MA2 = _t, MA3 = _t, BA1 = _t, BA2 = _t, BA3 = _t, BS1 = _t, BS2 = _t, BS3 = _t, BZ1 = _t, BZ2 = _t, BZ3 = _t]),
    #"Rimosse colonne" = Table.RemoveColumns(Origine,{"MA1", "MA2", "MA3", "BA1", "BA2", "BA3", "BS1", "BS2", "BS3", "BZ1", "BZ2", "BZ3"}),
    tx4=Table.ToColumns(Table.Repeat(#"Rimosse colonne",4)),
    fcols=Table.ColumnNames(#"Rimosse colonne"),    
    cols=Table.ColumnNames(Origine),

    tab=List.Accumulate(cols, [MA={},BA={},BS={},BZ={}],(s,c)=>if Text.StartsWith(c,"MA") then s&[MA=s[MA]&Table.Column(Origine,c)] else 
    if Text.StartsWith(c,"BA") then s&[BA=s[BA]&Table.Column(Origine,c)] else
    if Text.StartsWith(c,"BS") then s&[BS=s[BS]&Table.Column(Origine,c)] else
    if Text.StartsWith(c,"BZ") then s&[BZ=s[BZ]&Table.Column(Origine,c)] else s
    )
in
    Table.FromColumns(tx4&Record.FieldValues(tab),fcols&Record.FieldNames(tab))

should work for any number of columns with the names ba1, ba2, .., Bs, Bs2, etc ... BZ1, BZ2, .... etc

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBDoUgDESvYlibn9IC6pIDeALC/a/xnQpaiAlM2jI8Sktx3q2O/O9aTIwEO0NOFYYI6kSqEervGIKcEUVIgmzk6locf6J5oC4v2T+5emHx+yXYhx4BChOxgcKXxfJeJrfMnvRaUlyYcbHjFkvTZubqPsSg4TKJoeFKDsOPdax9mMolac7Q3t+eXwOaZqiOMQ48M0bqBGm03urdbq1/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PK = _t, Date = _t, AG = _t, ART = _t, MA1 = _t, MA2 = _t, MA3 = _t, BA1 = _t, BA2 = _t, BA3 = _t, BS1 = _t, BS2 = _t, BS3 = _t, BZ1 = _t, BZ2 = _t, BZ3 = _t]),
    #"Duplicata colonna" = Table.DuplicateColumn(Origine, "PK", "PK - Copia"),
    #"Duplicata colonna1" = Table.DuplicateColumn(#"Duplicata colonna", "PK", "PK - Copia.1"),
    #"Duplicata colonna2" = Table.DuplicateColumn(#"Duplicata colonna1", "Date", "Date - Copia"),
    #"Duplicata colonna3" = Table.DuplicateColumn(#"Duplicata colonna2", "Date - Copia", "Date - Copia - Copia"),
    #"Duplicata colonna4" = Table.DuplicateColumn(#"Duplicata colonna3", "AG", "AG - Copia"),
    #"Duplicata colonna5" = Table.DuplicateColumn(#"Duplicata colonna4", "AG - Copia", "AG - Copia - Copia"),
    #"Duplicata colonna6" = Table.DuplicateColumn(#"Duplicata colonna5", "ART", "ART - Copia"),
    #"Duplicata colonna7" = Table.DuplicateColumn(#"Duplicata colonna6", "ART - Copia", "ART - Copia - Copia"),
    #"Riordinate colonne" = Table.ReorderColumns(#"Duplicata colonna7",{"PK", "PK - Copia", "PK - Copia.1","Date", "Date - Copia", "Date - Copia - Copia", "AG", "AG - Copia", "AG - Copia - Copia", "ART", "ART - Copia", "ART - Copia - Copia",  "MA1", "MA2", "MA3", "BA1", "BA2", "BA3", "BS1", "BS2", "BS3", "BZ1", "BZ2", "BZ3"}),
    #"Intestazioni abbassate di livello" = Table.DemoteHeaders(#"Riordinate colonne"),
    #"Trasposta colonna" = Table.Transpose(#"Intestazioni abbassate di livello"),
    #"Raggruppate righe" = Table.Group(#"Trasposta colonna", {"Column1"}, {{"all", each List.Combine(Table.ToColumns(Table.PromoteHeaders(Table.Transpose(_))))}}, GroupKind.Local,(x,y)=>Number.From(Text.Start(x[Column1],2)<>Text.Start(y[Column1],2))),
    tfc=Table.FromColumns(#"Raggruppate righe"[all],#"Raggruppate righe"[Column1])
in
   tfc

[Anonymous · Accepted solution]

let

    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBDoUgDESvYlibn9IC6pIDeALC/a/xnQpaiAlM2jI8Sktx3q2O/O9aTIwEO0NOFYYI6kSqEervGIKcEUVIgmzk6locf6J5oC4v2T+5emHx+yXYhx4BChOxgcKXxfJeJrfMnvRaUlyYcbHjFkvTZubqPsSg4TKJoeFKDsOPdax9mMolac7Q3t+eXwOaZqiOMQ48M0bqBGm03urdbq1/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PK = _t, Date = _t, AG = _t, ART = _t, MA1 = _t, MA2 = _t, MA3 = _t, BA1 = _t, BA2 = _t, BA3 = _t, BS1 = _t, BS2 = _t, BS3 = _t, BZ1 = _t, BZ2 = _t, BZ3 = _t]),
    #"Intestazioni abbassate di livello" = Table.DemoteHeaders(Origine),
    #"Trasposta colonna" = Table.Transpose(#"Intestazioni abbassate di livello"),
    trtab=(tab)=> 
    let 
    t1=Table.ToColumns(Table.PromoteHeaders(Table.Transpose(tab)))
    in if tab[Column1]{0}="PK" then List.Combine(List.Repeat(t1,4)) else List.Combine(t1),
    #"Raggruppate righe" = Table.Group(#"Trasposta colonna", {"Column1"}, 
    {{"all", each trtab(_)}}, GroupKind.Local,(x,y)=>Number.From(Text.Start(x[Column1],2)<>Text.Start(y[Column1],2))),

    tfc=Table.FromColumns(#"Raggruppate righe"[all],#"Raggruppate righe"[Column1]),
    #"Ordinate righe" = Table.Sort(tfc,{{"PK", Order.Ascending}, {"Date", Order.Descending}}),
    #"Ricopiato in basso" = Table.FillDown(#"Ordinate righe",{"Date", "AG", "ART"})
in
    #"Ricopiato in basso"

[edhans]

Hmmm.... I've never used the Table.Repeat() function. Interesting.