Forum Discussion
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...
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"
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"
Thank you very much, what I needed exactly