Transpose the table with specific rule

AlienSx

Thanks for your prompt response

LK0913 ,

I have modified the M code and uploaded the PBIX file here. Could you please review it and let us know your feedback?

We truly appreciate your continued engagement and thank you for being an active and valued member of the community.

Best Regards,

Lakshmi.

Hi LK0913, another solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bclLCsAgDEXRvWQs1MTE1KGfXYj730aTQimCb/AG98wJtVYIQET2GK94XxRJYIWPEPH9A6WUnGij1ppFZnZKBxIRJ96o924x29xkszGGxVKKUz6QqjrpT+sB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SHO = _t, SO = _t, #"SO Date" = _t]),
    ColNames = List.Buffer(List.Skip(Table.ColumnNames(Source))),
    Transformed = Table.Combine(Table.Group(Source, {"SHO"}, {{"T", each
        [ a = List.Alternate(List.Combine(Table.ToRows(_)), 1, List.Count(ColNames), 0),
          b = List.Accumulate({1..(List.Count(a) / List.Count(ColNames))}, {}, (st,cur)=> st & List.Transform(ColNames, (x)=> let h1 = Text.Split(x, " ") in h1{0} & Text.From(cur) & " " & Text.Combine(List.Skip(h1), " ") )) ,
          c = Table.FromRows({ {[SHO]{0}} & a }, {"SHO"} & b)
        ][c], type table}}, 0)[T])
in
    Transformed

Yes, it works in the way i expected and i wanted, thank you for your effort. I am wondering it writting a M code is only way to reach this or perharps there is someething that i can do myself step by step ? Thanks 🙂