Forum Discussion

WLou's avatar
WLou
Helper I
6 years ago
Solved

Data Transformation pivot/unpivot?

Hi all, 

 

Below is a sample data in question

I'm trying to move those Row in bold to the left hand side and repeat to match the rest of the rows 

I can manage the rest but really stuck at moving them to the left

 

Any thoughts?  

 

FROM

ADM-1120AdminYES
926-364030.00% 
927-364030.00% 
921-364040.00% 
----------  
Total Distribution Percentage:100.00% 
3QR-30501MJ3QRYES
922-305030.00% 
926-305030.00% 
930-305040.00% 
----------  
Total Distribution Percentage:100.00% 

8BJ-7610

1LT8BJYES
110-761089.00% 
100-761011.00% 
----------  
Total Distribution Percentage:100.00% 

 

TO

ADM-1120Admin926-364030.00%
ADM-1120Admin927-364030.00%
ADM-1120Admin921-364040.00%
3QR-30501MJ3QR922-365030.00%
3QR-30501MJ3QR926-365030.00%
3QR-30501MJ3QR930-365040.00%
B8J-76101LTB8J110-761089.00%
B8J-76101LTB8J100-761011.00%

 

Regards,

Wendy

Need Help
  • ziying35's avatar
    ziying35
    6 years ago
    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("xdDLCoJAFAbgV5FZOzLHsdHcVbaRhC5uIlpYSQjTCDauondPicZLKm6i2Z3D98Oc//BAi5QDctHMCzCASZBebsxyc7kl4j3SYtwvd+ipKz81GabMqjwxqLJaU9qjJbSl1S2xetV/NdRtw1RGXPOSu8ySUy6TVGjrODvHQkbX2FV56E7TzRZTMqn1AoFfLAeKMZuBgXPZWElJW/69GGfuY5tBvZhVWCx7iwEgzQAxnOkHi5zzuiVfFqDH/vrk4ws=",BinaryEncoding.Base64),Compression.Deflate))),
    fx=(tbl)=>
    let
      sTbl = Table.RemoveLastN(tbl,2),
      p1 = sTbl[Col1]{0},
      p2 = sTbl[Col2]{0},
      toLists = List.Skip(Table.ToRows(sTbl))
    in 
      List.TransformMany(toLists,each {{p1,p2}},(x,y)=>y&List.RemoveLastN(x)),
    group = Table.Group(Source,"Col3",{"t",fx},0,(x,y)=>Byte.From(y="YES")),
    toTbl = #table(null,List.Combine(group[t])),
    chType = Table.TransformColumnTypes(toTbl,{{"Column4", Percentage.Type}})
in
    chType

    If this works for you, please mark it as the solution.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    My thought is that you data may take the cake for some of the ugliest I've ever seen. I mean, not to call your baby ugly but wow... Your saving grace may be those ---- rows.

    ImkeF , edhans any magic here?
  • ziying35's avatar
    ziying35
    Impactful Individual
    6 years ago
    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("xdDLCoJAFAbgV5FZOzLHsdHcVbaRhC5uIlpYSQjTCDauondPicZLKm6i2Z3D98Oc//BAi5QDctHMCzCASZBebsxyc7kl4j3SYtwvd+ipKz81GabMqjwxqLJaU9qjJbSl1S2xetV/NdRtw1RGXPOSu8ySUy6TVGjrODvHQkbX2FV56E7TzRZTMqn1AoFfLAeKMZuBgXPZWElJW/69GGfuY5tBvZhVWCx7iwEgzQAxnOkHi5zzuiVfFqDH/vrk4ws=",BinaryEncoding.Base64),Compression.Deflate))),
    fx=(tbl)=>
    let
      sTbl = Table.RemoveLastN(tbl,2),
      p1 = sTbl[Col1]{0},
      p2 = sTbl[Col2]{0},
      toLists = List.Skip(Table.ToRows(sTbl))
    in 
      List.TransformMany(toLists,each {{p1,p2}},(x,y)=>y&List.RemoveLastN(x)),
    group = Table.Group(Source,"Col3",{"t",fx},0,(x,y)=>Byte.From(y="YES")),
    toTbl = #table(null,List.Combine(group[t])),
    chType = Table.TransformColumnTypes(toTbl,{{"Column4", Percentage.Type}})
in
    chType

    If this works for you, please mark it as the solution.

    • WLou's avatar
      WLou
      Helper I
      6 years ago

      Yes it's working ! 

      Thanks so much ziying35 

      Could you please have a bit of explaination of what you did ?Especially the middle part!

       

      Wendy

       

       

       

       

      • ziying35's avatar
        ziying35
        Impactful Individual
        6 years ago

        Hi, Wendy.

        I made a local grouping, and fx is a custom function that is responsible for stitching the first two columns of data in the result table and cleaning up the unwanted data. I use translation software to communicate with you, I hope you can understand my meaning, too deep explanation I may not be able to express in English