Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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. |
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.