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