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.
Hi,
I have a table ActiveTable with rows:
ID START_DATE COMPLETE_DATE
1 01/31/2014 02/13/2024
2 02/14/2014 02/28/2024
3 02/14/2014 02/28/2024
4 02/14/2014 02/28/2024
I have a 2nd table(called CompareDate with just 1 row):
START DATE CALCENDATE
01/31/2014 02/16/2014
What I want is on the first table (ActiveTable) to filter down to ID's 1
So basically the logic is :
ACTIVETABLE_START_DATE >= COMPAREDATE_START DATE AND
ACTIVETABLE_START_DATE <= COMPAREDATE_CALCENDATE
Solved! Go to Solution.
hi @EaglesTony ,
steps taken:
1. create a duplicate of the comparedate query.
2. drilled down in 2 times, once of the start date and once on the calcendate column of the comparedate query. First drill will created a list, second one will covert it into a datetime value
3. Filtered the other query based on the input from the compared date query.
create 3 blank queries and copy paste the below:
1.CompareDate_Start
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzbUNzIwNFHSUTIw0jc0g3BiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"START DATE" = _t, COMPLETE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"START DATE", type date}, {"COMPLETE", type date}}),
#"START DATE" = #"Changed Type"[START DATE],
#"START DATE1" = #"START DATE"{0}
in
#"START DATE1"
2. CompareDate_COMPLETE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzbUNzIwNFHSUTIw0jc0g3BiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"START DATE" = _t, COMPLETE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"START DATE", type date}, {"COMPLETE", type date}}),
COMPLETE = #"Changed Type"[COMPLETE],
COMPLETE1 = COMPLETE{0}
in
COMPLETE1
3. ActiveTable
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw0jc01jcyMDJB5cTqRCsZQYSMLJDkYRyQvDEBeRN88rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, START_DATE = _t, COMPLETE_DATE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"START_DATE", type date}, {"COMPLETE_DATE", type date}}),
// compare with start and complete date from drilled down values
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [START_DATE] >= CompareDate_Start and [COMPLETE_DATE] <= CompareDate_COMPLETE )
in
#"Filtered Rows"
Hi @EaglesTony ,
@adudani Good reply!
And you can also try this M function to add a custom column in the table ActiveTable:
if [START_DATE] >= List.Max(CompareDate[START DATE]) and [COMPLETE_DATE] <= List.Max(CompareDate[CALCENDATE]) then 1 else 0
Then you only need to filter the data for which the custom column = 1:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @EaglesTony ,
@adudani Good reply!
And you can also try this M function to add a custom column in the table ActiveTable:
if [START_DATE] >= List.Max(CompareDate[START DATE]) and [COMPLETE_DATE] <= List.Max(CompareDate[CALCENDATE]) then 1 else 0
Then you only need to filter the data for which the custom column = 1:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @EaglesTony ,
steps taken:
1. create a duplicate of the comparedate query.
2. drilled down in 2 times, once of the start date and once on the calcendate column of the comparedate query. First drill will created a list, second one will covert it into a datetime value
3. Filtered the other query based on the input from the compared date query.
create 3 blank queries and copy paste the below:
1.CompareDate_Start
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzbUNzIwNFHSUTIw0jc0g3BiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"START DATE" = _t, COMPLETE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"START DATE", type date}, {"COMPLETE", type date}}),
#"START DATE" = #"Changed Type"[START DATE],
#"START DATE1" = #"START DATE"{0}
in
#"START DATE1"
2. CompareDate_COMPLETE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzbUNzIwNFHSUTIw0jc0g3BiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"START DATE" = _t, COMPLETE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"START DATE", type date}, {"COMPLETE", type date}}),
COMPLETE = #"Changed Type"[COMPLETE],
COMPLETE1 = COMPLETE{0}
in
COMPLETE1
3. ActiveTable
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw0jc01jcyMDJB5cTqRCsZQYSMLJDkYRyQvDEBeRN88rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, START_DATE = _t, COMPLETE_DATE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"START_DATE", type date}, {"COMPLETE_DATE", type date}}),
// compare with start and complete date from drilled down values
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [START_DATE] >= CompareDate_Start and [COMPLETE_DATE] <= CompareDate_COMPLETE )
in
#"Filtered Rows"