Forum Discussion

data1234's avatar
data1234
Frequent Visitor
2 years ago
Solved

Pivoting/Grouping Dates

I have a table from a SQL database with a bunch of products that are repaired multiple times, each item has a serial number and has up to n repairs each in its respective row I want to group by serial number and then have each product's serial number have all of the repair dates as columns in a row per serial number up to some number n of repairs per item e.g.

Serial_nrRepair_date
A123415/02/2024
A123418/02/2024
A123415/03/2024
A222212/02/2024
A555511/01/2024
A555511/08/2024

IT should turn into this for some repair dates per serial_nr N

Serial_nrRepair_date1 Repair_date2 Repair_date3
A123415/02/202418/02/202415/03/2024
A222212/02/2024nullnull
A555511/01/202411/08/2024null

 

Thank you very much

Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    pls try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MjZR0lEyNNUzMNIzMjAyUYrVQRK2wC4MVG2MJGwEBCBhI1TVpkAAEjbUMzDELmwBFY4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Serial_nr = _t, Repair_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial_nr", type text}, {"Repair_date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Serial_nr"}, {{"Count", 
    (x)=>[
          a=  List.Count(x[Repair_date]),
          b=  Table.FromRows( {x[Repair_date]}, List.Transform( {1..a}, (x)=> "Repair_date" & Text.From(x)))
    ][b]
}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Repair_date1", "Repair_date2", "Repair_date3"})
in
    #"Expanded Count"

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MjZR0lEyNNUzMNIzMjAyUYrVQRK2wC4MVG2MJGwEBCBhI1TVpkAAEjbUMzDELmwBFY4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Serial_nr = _t, Repair_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial_nr", type text}, {"Repair_date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Serial_nr"}, {{"Count", 
    (x)=>[
          a=  List.Count(x[Repair_date]),
          b=  Table.FromRows( {x[Repair_date]}, List.Transform( {1..a}, (x)=> "Repair_date" & Text.From(x)))
    ][b]
}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Repair_date1", "Repair_date2", "Repair_date3"})
in
    #"Expanded Count"
    • data1234's avatar
      data1234
      Frequent Visitor
      2 years ago

      Thank you very much, what I needed exactly

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    did not bother with column names...

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    group = Table.Group(Source, "Serial_nr", {"x", (x) => Table.FromRows({{Table.FirstValue(x)} & x[Repair_date]})}), 
    result = Table.Combine(group[x])
in
    result