Transpose the table with specific rule

[LK0913]

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

[dufoq3]

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]

This one also works perfect, thanks.

[AlienSx]

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]

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]

@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]

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 🙂 

[v-lgarikapat]

Hi @LK0913 ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

Best Regards,
Lakshmi.

[v-lgarikapat]

@LK0913 ,

Thank you for the confirmation. We’re glad to hear that.

Best Regards,

Lakshmi

[v-lgarikapat]

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]

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

[LK0913]

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

[jaineshp]

Hey @LK0913,

Here’s how you can achieve the result (Image 2) from your source table (Image 1). Keeping it short, clear, and practical like a Fabric Community reply:

Steps:

1. Load your table into Power Query.

2. Select SHO column → keep it as key.

3. Use Add Index Column (starting from 1).

4. Pivot the table:

   • Column to pivot → Index

   • Values → SO and SO Date

5. Rename columns as SO1, SO1 Date, SO2, SO2 Date…

That’s it — now each SHO will have all SO legs in one row as you wanted.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer