Forum Discussion

LK0913's avatar
LK0913
Regular Visitor
11 months 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 legs, i need each leg to be specific column, date as well.

Here is a table: 

Here is what i need to reach:

Thanks

Need Help
Show and Tell
  • dufoq3's avatar
    dufoq3
    11 months 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
    11 months 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 🙂 

11 Replies

  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    11 months ago

    Hi LK0913 ,

    Thanks for reaching out to the Microsoft fabric community forum.

    jaineshp ,

    Thanks for your prompt response

    Based on the sample data provided, I have implemented the required logic and attached the PBIX file for your review.
    Could you please review the solution and share your feedback?

    Thank you for your time and support.

    Best regards,
    Lakshmi

     

     

    • LK0913's avatar
      LK0913
      Regular Visitor
      11 months ago

      Hi Lakshmi,

      This is not yet what I am trying to have at the end, Point is that I want to be one line per SHO, like this:

      Since SO1 represent first leg od SHO which occurs earliest and therefore has the smallest date, etc..

       

       

  • AlienSx's avatar
    AlienSx
    Super User
    11 months 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
      11 months 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
        11 months 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
    Community Champion
    11 months 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
    Regular Visitor
    11 months ago

    Hey Jaineshp,

    This approach creates an table with 8 culumns (for each index number), in stead of 6 new columns (3 for SO and 3 for SO Date). Also, SHO shoul be in single line, and in that line SO ans SO Dates