Forum Discussion
Solved
How to add two separate Rank Column based on two criteria?
Hi,
In below table, I need value base ranking by country & cust state wise in power query...
| Customer No. | Customer Name | Product Code | Product Description | Value | County | Cust State | Country Rank | State Rank |
| 98717232 | Abishek Enterprises | 3457652 | IMBUSH 10x4X250 ML | 238339 | IN | MP | ||
| 98742618 | NATARAJA STORES | 3457144 | CARATE 4x25X100 ML | 2034283 | IN | MP | ||
| 98742618 | NATARAJA STORES | 3493457 | FANTIS 10X1 L | 324930 | IN | MP | ||
| 98743861 | BALAJI TRADER | 3437662 | CARATE 2x5X1 L | 349834 | IN | GJ | ||
| 98743861 | BALAJI TRADER | 3457646 | IMBUSH 5x2X1 L | 3487 | IN | GJ | ||
| 98718113 | Meenashi Services | 3459175 | SIFLEX 25X400 ML | 346837 | IN | GJ | ||
| 98735175 | BRUNA Co.Pvt. Ltd | 3469469 | AMISTAR TOP 20X500 ML | 56089 | IN | TG | ||
| 98735178 | amarapuri Enterprises | 3457361 | ABION 40X250 ML | 349837 | IN | TG | ||
| 98735178 | amarapuri Enterprises | 3457362 | ABION 20X500 ML | 546089 | IN | TG | ||
| 98735178 | amarapuri Enterprises | 3457363 | ABION 10X1 L | 2465322 | IN | TG | ||
| 98735178 | amarapuri Enterprises | 3457653 | IMBUSH 25x4X100 ML | 3095867 | IN | TG | ||
| 98744355 | PAVITHRA AGENCY | 3457483 | ARAIFIT 50 EC 20x500 ML | 4985495 | IN | TG | ||
| 98744355 | PAVITHRA AGENCY | 3457579 | ABION 2X5 L | 348734 | IN | TG |
I have tried for solution by Grouping option in power query result shows only country ranking.
Just for Example:- below First 4 Column is summarized with Pivot & then last 2 column added manually
| Customer No. | Customer Name | Cust State | Value | Country Rank | State Rank |
| 98735178 | amarapuri Enterprises | TG | 6457115 | 1 | 1 |
| 98744355 | PAVITHRA AGENCY | TG | 5334229 | 2 | 2 |
| 98742618 | NATARAJA STORES | MP | 2359213 | 3 | 1 |
| 98743861 | BALAJI TRADER | GJ | 353321 | 4 | 1 |
| 98718113 | Meenashi Services | GJ | 346837 | 5 | 2 |
| 98717232 | Abishek Enterprises | MP | 238339 | 6 | 2 |
| 98735175 | BRUNA Co.Pvt. Ltd | TG | 56089 | 7 | 3 |
Hi ASG25, it is also possible in Power Query, but it is not the fastest one...
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZNba9tAEIX/yuLnEPYys1o9rl3ZWSPJRtoElZAHtxFEhKZBdoN+fmcVXRJiKEmFEMMM+zE65+zt7SI2kYikkouLhf3RHB/qR5Y8ner2uW2O9ZG6CjDSGOYuW16XV0zwDiqJnGUpNaUySsVhmtMn2y/uLnooSC0MdXLrbWG3lpV+VyTlABQAVK1o4hMGncRK8ABkgcgVSKM+h4wDlaq1zb0racdKsJSFJ4wlzfkZoDJaUGdpU7t1zBf2W1L0OBVpLecNZYfveBAbBSNvs/03jyQEPUuInRx5/dhEH2HCCBFEyOr66XB8aFhZty/Nz9GTWERIVenWaVIxEhAmARVoo84QFb6eWRbXuWWr35f7l9MlS0/3r2diekMKMleSvszv9kzyCvlgNGpuJp/95i00mHL4dWgPz3/a5kx8VK+KXbpdzoAP2ZmVjL5KlRN1WrSnIvzPrmqivk+RBI1Kyq9h6ehsv0S6QWIUVvEYjT4jAoDC4Nfe3jh/VVhmN0m++j4Qob8ilE+3dp6RpMmKZOgmv0hZhBg/i8UonlWtcPj9MaZz6gPv7i8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer No." = _t, #"Customer Name" = _t, #"Product Code" = _t, #"Product Description" = _t, Value = _t, Country = _t, #"Cust State" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}}), GroupedRows1 = Table.Group(ChangedType, {"Customer No.", "Customer Name", "Country", "Cust State"}, {{"Value", each List.Sum([Value]), type number}}), // Added State Rank inside [All] tables. GroupedRows2 = Table.Group(GroupedRows1, {"Country", "Cust State"}, {{"All", each Table.AddIndexColumn(Table.Sort(_, {{"Value", Order.Descending}}), "State Rank", 1, 1, Int64.Type), type table}}), CombinedAll1 = Table.Combine(GroupedRows2[All]), // Added Country Rank inside [All] tables. GroupedRows3 = Table.Group(CombinedAll1, {"Country"}, {{"All", each Table.AddIndexColumn(Table.Sort(_, {{"Value", Order.Descending}}), "Country Rank", 1, 1, Int64.Type), type table}}), CombinedAll2 = Table.Combine(GroupedRows3[All]) in CombinedAll2
2 Replies
Hi ASG25, it is also possible in Power Query, but it is not the fastest one...
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZNba9tAEIX/yuLnEPYys1o9rl3ZWSPJRtoElZAHtxFEhKZBdoN+fmcVXRJiKEmFEMMM+zE65+zt7SI2kYikkouLhf3RHB/qR5Y8ner2uW2O9ZG6CjDSGOYuW16XV0zwDiqJnGUpNaUySsVhmtMn2y/uLnooSC0MdXLrbWG3lpV+VyTlABQAVK1o4hMGncRK8ABkgcgVSKM+h4wDlaq1zb0racdKsJSFJ4wlzfkZoDJaUGdpU7t1zBf2W1L0OBVpLecNZYfveBAbBSNvs/03jyQEPUuInRx5/dhEH2HCCBFEyOr66XB8aFhZty/Nz9GTWERIVenWaVIxEhAmARVoo84QFb6eWRbXuWWr35f7l9MlS0/3r2diekMKMleSvszv9kzyCvlgNGpuJp/95i00mHL4dWgPz3/a5kx8VK+KXbpdzoAP2ZmVjL5KlRN1WrSnIvzPrmqivk+RBI1Kyq9h6ehsv0S6QWIUVvEYjT4jAoDC4Nfe3jh/VVhmN0m++j4Qob8ilE+3dp6RpMmKZOgmv0hZhBg/i8UonlWtcPj9MaZz6gPv7i8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer No." = _t, #"Customer Name" = _t, #"Product Code" = _t, #"Product Description" = _t, Value = _t, Country = _t, #"Cust State" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}}), GroupedRows1 = Table.Group(ChangedType, {"Customer No.", "Customer Name", "Country", "Cust State"}, {{"Value", each List.Sum([Value]), type number}}), // Added State Rank inside [All] tables. GroupedRows2 = Table.Group(GroupedRows1, {"Country", "Cust State"}, {{"All", each Table.AddIndexColumn(Table.Sort(_, {{"Value", Order.Descending}}), "State Rank", 1, 1, Int64.Type), type table}}), CombinedAll1 = Table.Combine(GroupedRows2[All]), // Added Country Rank inside [All] tables. GroupedRows3 = Table.Group(CombinedAll1, {"Country"}, {{"All", each Table.AddIndexColumn(Table.Sort(_, {{"Value", Order.Descending}}), "Country Rank", 1, 1, Int64.Type), type table}}), CombinedAll2 = Table.Combine(GroupedRows3[All]) in CombinedAll2