[Power Query] - Combine 2 queries/tables with valid From and to date fields

[Bullstrik1]

Hi everyone!

I'v been googling this issue for quite some time now, but I wasn't able to find anything related for porwer query so far, and thats why i'm counting with some of your experts help.

To the point:

I have 2 tables i would like to join, based on some date fields (I'm sorry about the unexistant formatting but i don't know how to post Excel tables in this forum, but i'll be glad to post an example excel workbook if you tell me how to :P )

Fact table:

[TABLE="width: 335"]
<tbody>[TR]
[TD]Ocurrence[/TD]
[TD]Object[/TD]
[TD]Entry Date[/TD]
[TD]Out Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]08-02-2017[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]07-02-2017[/TD]
[TD="align: right"]07-02-2017[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]24-12-2016[/TD]
[TD="align: right"]24-12-2016[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15-01-2017[/TD]
[TD="align: right"]15-01-2017[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]16-01-2017[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 249"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Dimensions table:

[TABLE="width: 287"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Object[/TD]
[TD]DateFrom[/TD]
[TD]DateTo[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]05-02-2017[/TD]
[TD="align: right"]15-02-2017[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]01-01-2017[/TD]
[TD="align: right"]31-01-2017[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]01-12-2016[/TD]
[TD="align: right"]04-02-2017[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]05-02-2017[/TD]
[TD="align: right"]31-12-2100
[/TD]
[/TR]
</tbody>[/TABLE]

From this 2 tables, i would like to create a join that would return the folowing table:

[TABLE="width: 581"]
<tbody>[TR]
[TD]Ocurrence[/TD]
[TD]Entry Date[/TD]
[TD]Out Date[/TD]
[TD]Person[/TD]
[TD]Object[/TD]
[TD]DateFrom[/TD]
[TD]DateTo[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD="align: right"]08-02-2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]05-02-2017[/TD]
[TD="align: right"]15-02-2017[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]15-01-2017[/TD]
[TD="align: right"]15-01-2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]01-01-2017[/TD]
[TD="align: right"]31-01-2017[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD="align: right"]16-01-2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]01-01-2017[/TD]
[TD="align: right"]31-01-2017[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]24-12-2016[/TD]
[TD="align: right"]24-12-2016[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]01-12-2016[/TD]
[TD="align: right"]04-02-2017[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]07-02-2017[/TD]
[TD="align: right"]07-02-2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]05-02-2017[/TD]
[TD="align: right"]31-12-2100[/TD]
[/TR]
</tbody>[/TABLE]

Note:
As you can see, sometimes Entry Date is null and there is no way one can retreive that information.

Anyone have an idea how one could do this using power query?

I saw this post with somehting similar to what i want to do but for SQL:
https://blog.oraylis.de/2014/11/com...d-fromto-date-ranges-into-a-single-dimension/
Hopefully it will provide some better insight on what i would like to do.

Thank you, in advance for your help and fr reading this post!

 

[MarcelBeug]

As a first reply: maybe the answer in this post might be helpful. It looks somewhat similar.

Otherwise, for your specific case, can you provide some more information on the logic of combining the 2 tables (which dates should be before/after which dates, also in case of missing dates (which dates can be missing?), is any overlap possible, i.e. mulitple hits, etcetera).

Remark: I probably won't be available for any further feedback during the next 8 hours or so.

 

[MarcelBeug]

I created the tables Facts and Dimensions.
And the function GetPerson:

(Object as number, Out as date, optional Entry as date) => 
        Table.SelectRows(Dimensions, 
                         each [Object] = Object and [DateFrom] <= Out and 
                              (if Entry = null then [DateTo] >= Out 
                                               else [DateTo] >= Entry))

And the following query:

let
    Source = Facts,
    #"Invoked Custom Function" = Table.AddColumn(Source, "GetPerson", each GetPerson([Object], [Out Date], [Entry Date])),
    #"Expanded GetPerson" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetPerson", {"Person", "DateFrom", "DateTo"}, {"Person", "DateFrom", "DateTo"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded GetPerson",{"Occurrence", "Entry Date", "Out Date", "Person", "Object", "DateFrom", "DateTo"})
in
    #"Reordered Columns"

Apart from the sort order (which I don't understand) this produces the output according to your example.

 

[Bullstrik1]

Hi everyone!

MarcelBeug, thank you so very much for your reply and help!
I haven't tested your queries yet (lack of time i'm so sorry), but i will test it soon enough.

The logic/rules of combining the 2 tables is the folowing:

1. One person is owner of a certain object in a given time span;
2. One person can only have one single object in a certain time spam;
3. While the person is owning the object in a given time span all ocurrences, in the same time span, must be linked to that same person;
4. All ocurrences have an Out date, but it can happen to not have and Entry date
5. One ocurrence max time span in 24h, or one single day - its not possible one ocurrence to have Entry date on day x and Out date on day x+n, being n any natural number superior or equal to 1

Thank you!

 

[Bullstrik1]

Hi everyone!

MarcelBeug, thank you so very much for your reply and help!
I haven't tested your queries yet (lack of time i'm so sorry), but i will test it soon enough.

The logic/rules of combining the 2 tables is the folowing:

1. One person is owner of a certain object in a given time span;
2. One person can only have one single object in a certain time spam;
3. While the person is owning the object in a given time span all ocurrences, in the same time span, must be linked to that same person;
4. All ocurrences have an Out date, but it can happen to not have and Entry date
5. One ocurrence max time span in 24h, or one single day - its not possible one ocurrence to have Entry date on day x and Out date on day x+n, being n any natural number superior or equal to 1

Thank you!

Hi everyone!

I finally had the time to test this out.
It works like a charm!

I just need to test this with my real data now, but i'm sure it will be allright, and if not i'll post back here.

And thanks again Marcel!

Cheers