Forum Discussion
Solved
Unpivot
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 star...
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"
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
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
tfclet 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"