Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TariqArnabi
Frequent Visitor

Custom column that retrieves the Min date after "Suspend" date

I'm trying to retrieve the minimum date AFTER the tasks moves out of suspense 

so currently the columns are as follows (The Out of Suspend will be the custom column added)

TariqArnabi_0-1657560443001.png

 

What I'll like to retrieve via M language is to retrieve the next date after the a task went into Suspend 
example 
Task 1, went into suspend 08/15/2021 - So out of suspend date will be 09/30/2021
Task 2, went into suspend 01/10/2022 - So out of suspend date will be 02/7/2022
Task 3, went into suspend 10/26/2021 - So out of suspend willbe 11/22/2021

I utilized the following to create a custome column that gave me a differece in date, where i added the difference to retrieve the closest date.    Suspend is 10/26/2021 - Closes date is 11/15/2021 = 19:21:48:01  ..
Only issue i havent been able to convert that back to a date, or add it to the 10/26/2021 

IF [Suspend] < (List.Max({[Submitted,[#"Quote"],[Processed],[Proposed],[Waiting ]}))
THEN (List.Max({[Submitted],[#"Quote"],[Binding Processed],[Proposed],[Waiting]})) -
[Suspend] ELSE NULL

Is there a better more efficient way to do it? 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @TariqArnabi ,

 

I duplicated the original table as Table 2and used Unpivot -->Group--Add column(use Try..Otherwise to  handle errorsto get such output:

Eyelyn9_0-1657767868468.png

Then go back to the original table, "Merge Queries" to get the matched [Out of Suspend] column:

Eyelyn9_1-1657767958394.png

 

Below is the whole M syntax:

For Table 2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/BDcAgDAN3ybtSiENbmAWx/xo1glAkHjkbB9OamFySXgUUCQE1gKeqp4GYWNTu6faryRATpbLT0Dduu9pOYr2wAvAdME7TGQudktlRZzC0RJZzDovb6zHj+XtlSo+aR4xWOT7BigdJ7x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskID = _t, Submitted = _t, Quote = _t, Processed = _t, Proposed = _t, Waiting = _t, Suspend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskID", Int64.Type}, {"Submitted", type date}, {"Quote", type date}, {"Processed", type date}, {"Proposed", type date}, {"Waiting", type date}, {"Suspend", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TaskID", "Suspend"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"TaskID", "Suspend"}, {{"All", each _, type table [TaskID=nullable number, Suspend=nullable date, Attribute=text, Value=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Out of Suspend", each try Table.Min( Table.SelectRows([All], each [Suspend]<[Value]),"Value" )[Value] otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All", "Suspend"})
in
    #"Removed Columns"

For Original table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/BDcAgDAN3ybtSiENbmAWx/xo1glAkHjkbB9OamFySXgUUCQE1gKeqp4GYWNTu6faryRATpbLT0Dduu9pOYr2wAvAdME7TGQudktlRZzC0RJZzDovb6zHj+XtlSo+aR4xWOT7BigdJ7x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskID = _t, Submitted = _t, Quote = _t, Processed = _t, Proposed = _t, Waiting = _t, Suspend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskID", Int64.Type}, {"Submitted", type date}, {"Quote", type date}, {"Processed", type date}, {"Proposed", type date}, {"Waiting", type date}, {"Suspend", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"TaskID"}, #"Table 2", {"TaskID"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Out of Suspend"}, {"Out of Suspend"})
in
    #"Expanded Table 2"

Refer to:

Error handling - Power Query | Microsoft Docs

 

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-eqin-msft
Community Support
Community Support

Hi @TariqArnabi ,

 

I duplicated the original table as Table 2and used Unpivot -->Group--Add column(use Try..Otherwise to  handle errorsto get such output:

Eyelyn9_0-1657767868468.png

Then go back to the original table, "Merge Queries" to get the matched [Out of Suspend] column:

Eyelyn9_1-1657767958394.png

 

Below is the whole M syntax:

For Table 2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/BDcAgDAN3ybtSiENbmAWx/xo1glAkHjkbB9OamFySXgUUCQE1gKeqp4GYWNTu6faryRATpbLT0Dduu9pOYr2wAvAdME7TGQudktlRZzC0RJZzDovb6zHj+XtlSo+aR4xWOT7BigdJ7x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskID = _t, Submitted = _t, Quote = _t, Processed = _t, Proposed = _t, Waiting = _t, Suspend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskID", Int64.Type}, {"Submitted", type date}, {"Quote", type date}, {"Processed", type date}, {"Proposed", type date}, {"Waiting", type date}, {"Suspend", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TaskID", "Suspend"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"TaskID", "Suspend"}, {{"All", each _, type table [TaskID=nullable number, Suspend=nullable date, Attribute=text, Value=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Out of Suspend", each try Table.Min( Table.SelectRows([All], each [Suspend]<[Value]),"Value" )[Value] otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All", "Suspend"})
in
    #"Removed Columns"

For Original table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/BDcAgDAN3ybtSiENbmAWx/xo1glAkHjkbB9OamFySXgUUCQE1gKeqp4GYWNTu6faryRATpbLT0Dduu9pOYr2wAvAdME7TGQudktlRZzC0RJZzDovb6zHj+XtlSo+aR4xWOT7BigdJ7x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskID = _t, Submitted = _t, Quote = _t, Processed = _t, Proposed = _t, Waiting = _t, Suspend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskID", Int64.Type}, {"Submitted", type date}, {"Quote", type date}, {"Processed", type date}, {"Proposed", type date}, {"Waiting", type date}, {"Suspend", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"TaskID"}, #"Table 2", {"TaskID"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Out of Suspend"}, {"Out of Suspend"})
in
    #"Expanded Table 2"

Refer to:

Error handling - Power Query | Microsoft Docs

 

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

TariqArnabi
Frequent Visitor

So i was able to edit the query and i believe i got it coming in correctly, only issue is that if the suspend is null, it comes as an error

IF [Suspend] <= (List.Max({[Submitted,[#"Quote"],[Processed],[Proposed],[Waiting ]}))
THEN (List.Max({[Submitted],[#"Quote"],[Binding Processed],[Proposed],[Waiting]})) -
[Suspend] ELSE [Suspend]

Changed the type to Whole Number  then added another custome column with a 
= Date.AddDays ([Suspend], [Custom])   

I believe that is currently working, going thru the columns to make sure everything is correct 





Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors