How To Determine If A Record In 1 Dataset Has Records In Another That Were Open At The Time

Question

Ok, first and formost I'm a newbie here so apologies if this has already been answered but as we all phrase things different ways i couldn't find anything similar to my request....so,with that in mind this is an issue that I have encountered many times in my job but never been able to truely address it using Power Query.

I regulary have 2 x datasets to compare, [set 1] includes requests for support from our team and [set 2] shows people who were already reciving a service from us. Ther are MULTIPLES in both sets.

So what I need to do is identify for each row in [set 1] based on the "Client iD" if there was a "service" active in [set 2] at the time the "request" came through in [set 1]. If there was i'd return "Existing Customer", otherise "New Customer" in the cell with the formula.

I have included examples of both [set] structures in a link a couple of posts below...Also, I know the Excel formula (see below)

=IF(COUNT(FILTER(Set2!$A$2:$A$50000,(Sheet2!$A$2:$A$50000=A2)(Set2!$C$2:$C$50000<=D2)((Set2!$D$2:$D$50000>=D2)+(Set2!$D$2:$D$50000=""))))>0,"Existing","New")&" Customer"

which deals with this but am trying to learn what the M Code would be...

Ian

Answer 1

Hi @Ian Lyons

(it would be nice of you to revise the title of this request with something that better reflects your need => could help people who search the site)

1/ Format your 2 Sets as Excel Tables and name them Table1 and Table2 for now
2/ Load each Table as a Connection only
3/ Create a new blank query with the following code:

let  
    Source = Table.NestedJoin(Table1, {"Client Id"}, Table2, {"Client iD"}, "DatesTable2", JoinKind.LeftOuter),  
    Transformed = Table.TransformColumns(Source,  
        {"DatesTable2", each  
            let  
                ReplacedNullEnd = Table.TransformColumns(_,  
                    {"Date Agreement Ended", each if _ is null then Date.From(DateTime.LocalNow()) else _, type date}  
                ),  
                DatesList = Table.AddColumn(ReplacedNullEnd, "Dates", each  
                    List.Dates([Date Agreement Started], Duration.Days([Date Agreement Ended]-[Date Agreement Started])+1, #duration(1,0,0,0)),  
                    type list  
                ),  
                DistinctList = List.Union(DatesList[Dates])  
            in  
                if Table.IsEmpty(_) then {} else DistinctList,  
            type list  
        }  
    ),  
    ClientStatus = Table.AddColumn(Transformed, "Client Status At time Of Request", each  
        if List.Contains([DatesTable2], [Date Of Request]) then "Existing Customer"  
        else "New Customer",  
        type text  
    ),  
    RemovedDatesTable2 = Table.RemoveColumns(ClientStatus,{"DatesTable2"})  
in  
    RemovedDatesTable2  

This looks good to me with the data you exposed. Your file updated as described above is available here.
Any question/issue let me know & if problem solved please mark this reply as answer (can help others) - Thanks

Answer 2

Hi, thanks for the feedback. re the title its hard to condense the actual issue into the limited title length, but will give it another go.

Re as to how to present issues next time round, noted and thanks, as mentioned this is my very first post so good to know for future.

Many thanks for the reply as well will look at this.

Ian

Answer 3

Lz-3068 Just wanted to say many many thanks for taking the time to respond and for providing the answer. Worked my way through how you went about it and am very grateful.

Cheers

Ian