Merge timestamp table with startdate-enddate table

[aidenblake]

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?

[AlB · Accepted solution]

@aidenblake 

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.

[AlB]

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.

[aidenblake]

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.