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...
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.

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