Forum Discussion
Merge timestamp table with startdate-enddate table
I have two tables which I like to merge or at least connect to each other based on the date columns.
Both tables don't have any other columns in common
First Table contains a start - and enddate, with Variable-Values (1,2 and 3):
| StartDate | EndDate | Var1 | Var2 | Var3 |
| 25.03.2022 | 30.09.2022 | 1 | 3 | 5 |
| 01.10.2022 | 31.12.2022 | 2 | 4 | 6 |
My second table contains timestamp column, with other 3 variables:
| Timestamp | Var4 | Var5 | Var6 |
| 25.03.2022 13:45 | 1.5 | 3 | 4 |
| 25.03.2022 14:00 | 3.4 | 2 | 3 |
| 25.03.2022 14:15 | 3.4 | 4 | 5 |
| … | … | … | … |
| 31.12.2022 23:15 | 3.2 | 3 | 5 |
| 31.12.2022 23:30 | 4.3 | 3 | 4 |
| 31.12.2022 23:45 | 3.4 | 4 | 3 |
Now I want a resulting table, which looks like this:
| Timestamp | Var4 | Var5 | Var6 | Var1 | Var2 | Var3 |
| 25.03.2022 13:45 | 1.5 | 3 | 4 | 1 | 3 | 5 |
| 25.03.2022 14:00 | 3.4 | 2 | 3 | 1 | 3 | 5 |
| 25.03.2022 14:15 | 3.4 | 4 | 5 | 1 | 3 | 5 |
| … | … | … | … | … | … | … |
| 31.12.2022 23:15 | 3.2 | 3 | 5 | 2 | 4 | 6 |
| 31.12.2022 23:30 | 4.3 | 3 | 4 | 2 | 4 | 6 |
| 31.12.2022 23:45 | 3.4 | 4 | 3 | 2 | 4 | 6 |
How can I achieve this in PowerBI?
See it all at work in the attached file. Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5LCsAwCATQqwTXRXTUTa4Scv9r1KQfkHYxs/HBOAYhWIwhQFPrHq3RQcqRbRmneVTkXWQd2bOx2ZdovGQlNjFlxUVgD8E99EdsDTlb+aUSr0P5yzwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Var4 = _t, Var5 = _t, Var6 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Var4", type number}, {"Var5", Int64.Type}, {"Var6", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Timestamp", type datetime}}, "en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Table.SelectRows(Table1, (inner)=> Date.From([Timestamp])>= inner[StartDate] and Date.From([Timestamp])<= inner[EndDate])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Var1", "Var2", "Var3"}, {"Var1", "Var2", "Var3"}) in #"Expanded Custom"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
3 Replies
Hi aidenblake
Do you want this in DAX or in Power Query?
I guess the end_date on the first row of the first table should be 25.03.2022 instead?
Please share a sample of the first two tables in text-tabular format instead of on a screen cap so that the contents can be copied and a solution built
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
There were some mistakes, which are now corrected. In Power Query, but if it's easier in DAX, that would be fine for me too - I'm happy if there is even one solution.
See it all at work in the attached file. Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5LCsAwCATQqwTXRXTUTa4Scv9r1KQfkHYxs/HBOAYhWIwhQFPrHq3RQcqRbRmneVTkXWQd2bOx2ZdovGQlNjFlxUVgD8E99EdsDTlb+aUSr0P5yzwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Var4 = _t, Var5 = _t, Var6 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Var4", type number}, {"Var5", Int64.Type}, {"Var6", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Timestamp", type datetime}}, "en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Table.SelectRows(Table1, (inner)=> Date.From([Timestamp])>= inner[StartDate] and Date.From([Timestamp])<= inner[EndDate])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Var1", "Var2", "Var3"}, {"Var1", "Var2", "Var3"}) in #"Expanded Custom"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
