Forum Discussion
Solved
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...
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.
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.
