[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:

Ocurrence	Object	Entry Date	Out Date
A	1		08-02-2017
B	2	07-02-2017	07-02-2017
C	1	24-12-2016	24-12-2016
D	3	15-01-2017	15-01-2017
E	3		16-01-2017

<tbody>
</tbody>

<tbody>
</tbody>

Dimensions table:

Person	Object	DateFrom	DateTo
1	1	05-02-2017	15-02-2017
1	3	01-01-2017	31-01-2017
2	1	01-12-2016	04-02-2017
2	2	05-02-2017	31-12-2100

<tbody>
</tbody>

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

Ocurrence	Entry Date	Out Date	Person	Object	DateFrom	DateTo
A		08-02-2017	1	1	05-02-2017	15-02-2017
D	15-01-2017	15-01-2017	1	3	01-01-2017	31-01-2017
E		16-01-2017	1	3	01-01-2017	31-01-2017
C	24-12-2016	24-12-2016	2	1	01-12-2016	04-02-2017
B	07-02-2017	07-02-2017	2	2	05-02-2017	31-12-2100

<tbody>
</tbody>

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