Forum Discussion

Regular Visitor

3 years ago

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 by Location?

Operating Office	Location	Fruit 1	Fruit 1 Quantity	Fruit 2	Fruit 2 Quantity
HQ	East Market	Pear	4	Orange	6
Office 1	West Market	Lemon	10	Peach	3
HQ	North Market	Orange	20	Apple	14
HQ	Central Market	Apple	12	Mango	10
Office 2	South Market	Peach	4	Banana	6

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"

1 Reply

Community Champion

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"

Related content