Forum Discussion

LK0913's avatar
LK0913
Regular Visitor
1 year ago
Solved

Transpose the table with specific rule

Hello, I have a table with 3 columns, SHO, SO, SO Date, I need to make table with following columns: SHO, SO1, SO2,SO3... all in one row. Clarification: each SO is just a leg to SHO, SHO has several...
Need Help
Show and Tell
  • v-lgarikapat's avatar
    v-lgarikapat
    1 year ago

     

    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.

     

     

     

  • dufoq3's avatar
    dufoq3
    1 year ago

    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
  • LK0913's avatar
    LK0913
    1 year ago

    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 🙂 

AlienSx's avatar
AlienSx
Super User
1 year ago
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(
        Source, 
        "SHO", 
        {"rows", (x) => {Table.FirstValue(x)} & List.Combine(Table.ToList(Table.Sort(x, "SO Date"), List.Skip))}
    ),
    result = Table.FromList(
        group[rows],
        (x) => x, 
        {"SHO"} & 
            List.Combine(
                List.Transform(
                    List.Transform({1..(List.Max(List.Transform(group[rows], List.Count)) - 1) / 2}, Text.From),
                    (x) => {"SO" & x, "SO" & x & " Date"}
                )
            )
    )
in
    result
  • LK0913's avatar
    LK0913
    Regular Visitor
    1 year ago

    Hey AlienSx,

    This is exaclty what i need to get as final resulut, but in PowerBI, not excel, and this works, but its confusing to me, i cant make it myself, i mean i will not be abe to replicate it. Thanks

    • v-lgarikapat's avatar
      v-lgarikapat
      Community Support
      1 year ago

       

      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.

       

       

       

      • LK0913's avatar
        LK0913
        Regular Visitor
        1 year ago

        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 🙂