How to merge two tables based on time range

[sagdec1212]

Hi all, I have 2 tables A and B

Table A has item name and the imported date:

item	date
A	1/7/2024
A	2/7/2024
A	1/6/2024
B	1/7/2024
B	2/7/2024

Table B has the valid date and the storage time:

storage_time	time_from	time_end	item
7	1/6/2024	30/6/2024	A
3	1/7/2024	30/7/2024	A
1	1/1/2024	12/31/2024	B

I want to do a mapping which meets the rule that:

To check the imported date and see whether the imported date falls in any validity period. For example, If item A was imported on 2/6, then the storage time should be 7.

And my idea output would be:

store	date	storage_time
A	1/7/2024	7
A	2/7/2024	7
A	1/6/2024	3
B	1/7/2024	1
B	2/7/2024	1

Can I do this in power query editor? Because I need to transform the data further based on the storage_time

Thank you very much in advance!

[v-kaiyue-msft · Accepted solution]

Hi @sagdec1212 ,

Thanks for the reply from @bhanu_gautam , please allow me to provide another insight:

1. Merge two tables.

2. Expand Table B.

3. Add a custom column.

if [date] >= [Table B.time_from] and [date] <= [Table B.time_end] then 1 else 0

4. Filter the custom column for values ​​1.

5. Delete unnecessary columns.

let
    Source = Table.NestedJoin(#"Table A", {"item"}, #"Table B", {"item"}, "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(Source, "Table B", {"storage_time", "time_from", "time_end", "item"}, {"Table B.storage_time", "Table B.time_from", "Table B.time_end", "Table B.item"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table B", "Custom", each if [date] >= [Table B.time_from] and [date] <= [Table B.time_end] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table B.time_from", "Table B.time_end", "Table B.item", "Custom"})
in
    #"Removed Columns"

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sagdec1212]

thank you so much! I found it quite easy and clear!

[bhanu_gautam]

@sagdec1212 , You can try to use below steps 

first convert date and time_from and time_end to date data type

Then create a new custom column in table A use m code

m
= Table.AddColumn(#"Changed Type", "CustomJoinKey", each [item] & "|" & [date])

Similarly in Table B

     = Table.AddColumn(#"Changed Type", "CustomJoinKey", each [item] & "|" & [time_from] & "|" & [time_end])

Then merge both tables using Inner join and Custom join key as column then expand the columns

Filter the rows where the date from Table A falls within the time_from and time_end range from Table B.