Log In
Register
Home
New Member
January 4, 2023
Offline
Hello everyone,
I need your help to write a formula in Power Qwery
Attached is a table containing a list of Contact linked to customers. Some customers have several unique IDs
Here are the steps to do:
1. remove the customers with less than 5 employees
2. group the data by Contact and work for each Contact almost independently
3. for each Contact, identify a Customer ID that has the highest revenue among the other IDs of the same customer
If a Customer has only one ID, it is retrieved in this step
4. then among the remaining IDs, we recover the TOP2 Tot Revenue
The attached file shows the desired result and I'm looking for a solution in Power Qwery
To do so in Excel, I used a combination of 3 functions : IF+Filter+LARGE
Thank you in advance for your ideas
Moderators
January 31, 2022
Offline
Please try to upload the file(s) again. This time, don't forget to press "Start upload" before you submit your reply.
New Member
January 4, 2023
Offline
Hi,
Here is the file attached
Thanks
Trusted Members
October 18, 2018
Offline
With your table
let
Source = Excel.CurrentWorkbook(){[Name="Tbl_1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each [EmployeesNber] >= 5),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Contact"}, {{"Max Revenue", each List.Max([Total Revenue]), type number}})
in
#"Grouped Rows"
Duplicate your table above and delete all steps except Source.
Then merge your two queries.
let
Source = Excel.CurrentWorkbook(){[Name="Tbl_1"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"Contact", "Total Revenue"}, Tbl_1, {"Contact", "Max Revenue"}, "Tbl_1", JoinKind.LeftOuter),
#"Expanded Tbl_1" = Table.ExpandTableColumn(#"Merged Queries", "Tbl_1", {"Contact", "Max Revenue"}, {"Tbl_1.Contact", "Tbl_1.Max Revenue"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Tbl_1", each ([Tbl_1.Contact] null)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Total Revenue", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",2)
in
#"Kept First Rows"
Moderators
January 31, 2022
Offline
Thanks for uploading the file. Perhaps I misunderstood but I believe the attached file contains the solution that you are asking for. Let us know if that's not the case.
It creates a table line in the picture below, containing the 17 row you marked with "Y".
1 Guest(s)
