Data Transformation pivot/unpivot?

[WLou]

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-1120	Admin	YES
926-3640	30.00%
927-3640	30.00%
921-3640	40.00%
----------
Total Distribution Percentage:	100.00%
3QR-3050	1MJ3QR	YES
922-3050	30.00%
926-3050	30.00%
930-3050	40.00%
----------
Total Distribution Percentage:	100.00%
8BJ-7610	1LT8BJ	YES
110-7610	89.00%
100-7610	11.00%
----------
Total Distribution Percentage:	100.00%

TO

ADM-1120	Admin	926-3640	30.00%
ADM-1120	Admin	927-3640	30.00%
ADM-1120	Admin	921-3640	40.00%
3QR-3050	1MJ3QR	922-3650	30.00%
3QR-3050	1MJ3QR	926-3650	30.00%
3QR-3050	1MJ3QR	930-3650	40.00%
B8J-7610	1LTB8J	110-7610	89.00%
B8J-7610	1LTB8J	100-7610	11.00%

Regards,

Wendy

[ziying35 · Accepted solution]

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]

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]

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

[Greg_Deckler]

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?