Merge by condition and approximate matching in Power Query

[MCstudio]

Hi community!

I have got a question.

We have got clients, who do orders, and managers, who control orders. The final goal is to understand, which manager provide what volume of orders. But, during the time, clients' managers are changing.

We have got order table:

|Client|	|Order Date|	|Order Amount|
Cl.1	01/01/22	120
Cl.2	01/01/22	450
Cl.3	02/01/22	340
Cl.4	10/01/22	210
Cl.1	05/02/22	119
Cl.2	07/02/22	454
Cl.3	08/02/22	342
Cl.4	11/02/22	225

We have got managers' changing table:

|Client|	|Manager Change Date|	|Manager|
Cl.3	01/01/22	Mng.2
Cl.4	08/01/22	Mng.3
Cl.2	07/02/22	Mng.2
Cl.3	06/02/22	Mng.1
Cl.1	15/02/22	Mng.2

The default manager at start is Mng.1

At final, we must have, in the Power Query, a table like this:

|Client|	|Order Date|	|Order Amount|	|Manager|
Cl.1	01/01/22	120	Mng.1
Cl.2	01/01/22	450	Mng.1
Cl.3	02/01/22	340	Mng.2
Cl.4	10/01/22	210	Mng.3
Cl.1	05/02/22	119	Mng.1
Cl.2	07/02/22	454	Mng.2
Cl.3	08/02/22	342	Mng.1
Cl.4	11/02/22	225	Mng.3

I have faced to solutions in the net, with Full outer merge - Conditional column - Group by - Fill down, but they aren't too good. Let's try to finde more simple and better one.

Thank you in advance!

[Anonymous]

Could you elaborate on the concept expressed in the following sentence?

"I have faced to solutions in the net, with Full outer merge - Conditional column - Group by - Fill down, but they aren't too good. Let's try to finde more simple and better one."

[MCstudio]

I would like to find best practice to solve this problem. Marked solution seemed to me too long.

[Anonymous]

"Marked solution seemed to me too long."

What "marked solutions" are you referring to?

And what is "too long"? The execution time or the proposed scripts (the number of steps required)?

If you explain less sparingly what your case is (size of the tables at stake, for instance), maybe someone can help you.

[MCstudio]

The marked solution (Full outer merge - Conditional column - Group by - Fill down), which I have seen here https://www.youtube.com/watch?v=y3bOyNpJuXc

Too long - yes, I mean the number of steps.
My first table have got about 8M rows, the second 81k, and the both are going to increase.

[Anonymous]

"Too long - yes, I mean the number of steps."

So your problem is not the execution time, but the code size !?
In this case, @wdx223_Daniel's solution (which could also be reduced by eliminating a redundant instruction and, if your db is sorted, eliminate two steps) seems to me to be right for you.

[MCstudio]

Dear @Anonymous ,

the @wdx223_Daniel 's solution seems to be the best, but how I answered to the @wdx223_Daniel 's message, there is a minor problem, which time to time results in an error.

Yesterday, at home I tried the code, it worked. Today at the office, it didn't. I was trying to find out, what is the reason. The only difference I have found, is date format.

Data format:

Home PC - dd.mm.yyyy

Office PC - dd/mm/yy

I guess, at office, this code change days' and months' places and results in an error.

If you, or @wdx223_Daniel help me, to correct the code, which will work with both formats, I will be grateful.

[wdx223_Daniel]

let
    OrderTable = Excel.CurrentWorkbook(){[Name="OrderTable"]}[Content],
    ManagerTable = Excel.CurrentWorkbook(){[Name="ManagerTable"]}[Content],
    Custom1 = let
                 mt=Table.Group(
                                ManagerTable,
                                "Client",
                                {"n",each Table.ToRows(Table.Sort(_,{"Manager Change Date",1}))}
                               )
              in
                 Table.AddColumn(
                                 OrderTable,
                                 "Manager",
                                 each let
                                         a=mt{[Client=[Client]]}?[n]?
                                      in
                                        if a=null then "Mng.1"
                                        else List.Skip(a,(x)=>x{1}>[Order Date]){0}?{2}? ??"Mng.1"
                                )
in
    Custom1

[MCstudio]

An error appeared

[Anonymous]

I don't know if that's exactly what you're looking for (in the sense that being simple and "better" at the same time isn't common).
At least the functions you mentioned and which, for some reason, did not satisfy you are not used.

edit:

This solution assumes that the Change date manager table is sorted in ascending order.
Otherwise it can be easily adapted to the generic case.