Forum Discussion
Solved
Multiple type summing up by location
Hi, I would need enlightenment from the experts here on how can I do the following with power query. For below data, how can I generate the total of each fruits by Operating Office and drill down...
suggest to transform data like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ghU0lFyTSwuUfBNLMpOLQHyAlITi4CUCRD7FyXmpacCGWZKsTrRSv5paZnJqQqGQIHwVGQtPqm5+XlA2tAAoj85A0gbg/WALfDLLyrJQCiHG2sEUu9YUJCTqgDSbYLQ4ZyaV1KUmIPQA1YFUmQEJHyB+vMh9iG5CyQTnF+KbBPMLSDPOCXmASHEM7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Operating Office" = _t, Location = _t, #"Fruit 1" = _t, #"Fruit 1 Quantity" = _t, #"Fruit 2" = _t, #"Fruit 2 Quantity" = _t]), Custom1 = #table(List.FirstN(Table.ColumnNames(Source),2)&{"Fruit","Quantity"},List.TransformMany(Table.ToRows(Source),each List.Split(List.Skip(_,2),2),(x,y)=>List.FirstN(x,2)&y)), #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Operating Office", type text}, {"Location", type text}, {"Fruit", type text}, {"Quantity", Int64.Type}}) in #"Changed Type"
suggest to transform data like this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ghU0lFyTSwuUfBNLMpOLQHyAlITi4CUCRD7FyXmpacCGWZKsTrRSv5paZnJqQqGQIHwVGQtPqm5+XlA2tAAoj85A0gbg/WALfDLLyrJQCiHG2sEUu9YUJCTqgDSbYLQ4ZyaV1KUmIPQA1YFUmQEJHyB+vMh9iG5CyQTnF+KbBPMLSDPOCXmASHEM7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Operating Office" = _t, Location = _t, #"Fruit 1" = _t, #"Fruit 1 Quantity" = _t, #"Fruit 2" = _t, #"Fruit 2 Quantity" = _t]),
Custom1 = #table(List.FirstN(Table.ColumnNames(Source),2)&{"Fruit","Quantity"},List.TransformMany(Table.ToRows(Source),each List.Split(List.Skip(_,2),2),(x,y)=>List.FirstN(x,2)&y)),
#"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Operating Office", type text}, {"Location", type text}, {"Fruit", type text}, {"Quantity", Int64.Type}})
in
#"Changed Type"