Forum Discussion
How to merge two tables based on time range
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!
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 04. 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.
3 Replies
- bhanu_gautam
Super User
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 columnsFilter the rows where the date from Table A falls within the time_from and time_end range from Table B. 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 04. 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.
thank you so much! I found it quite easy and clear!
