cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ErikLe
New Member

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 starting point is:

 

PKDateAGARTMA1MA2MA3BA1BA2BA3BS1BS2BS3BZ1BZ2BZ3
101.01.20211A1M1M2M3100105110101520506070
201.01.20211A2M2M3 100113 11121889 
302.01.20212A3M3  1002 3  2 6
402.01.20215A3   1213   8  8
503.01.20213A4M2M3M1100102103345789
603.01.20211A5M3M1 100105735 88 

 

I tried serveral times to make it look like this:

PKDateAGARTMABABSBZ
101.01.20211A1M11001050
101.01.20211A1M21051560
101.01.20211A1M31102070
201.01.20211A2M2100118
201.01.20211A2M3113129
201.01.20211A2  18 
302.01.20212A3M310032
302.01.20212A3 2  
302.01.20212A3   6
402.01.20215A3 12  
402.01.20215A3 13  
402.01.20215A3  88
503.01.20213A4M210037
503.01.20213A4M310248
503.01.20213A4M110359
603.01.20211A5M310038
603.01.20211A5M110558
603.01.20211A5 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

1 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"

View solution in original post

4 REPLIES 4
Rocco_sprmnt21
Community Champion
Community Champion

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
   tfc

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"

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10

The Power BI Community Show

Join us on October 3 at 11 am PST when Amit Chandak, a Power BI Super User, will demo how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors