Conditional lookup in specific order with M Code

[HRWhite]

Hi all,

Forgive me for a quite specific request. I am trying to build a dataset which can calculate the number of days ahead or behind schedule a project has run. The dataset broadly follows this structure:

Project	Stage 1 planned completion date	Stage 1 actual completion date	Stage 1 days over schedule (calculated column which)	Stage 2 planned completion date	Stage 2 actual completion date	Stage 2 days over schedule (calculated column which)
Project A	01/03/22	04/03/22	3	01/04/22	01/04/22	0
Project B	24/12/22	20/12/22	-4	05/05/23	null	null

I would like a calculated colmun which returne the value for days over schedule for the most recent stage completed. So, in the example above, it would return 0 for Project A (because both stages 1 and 2 have been completed, with dates provided for both), but would return -4 for Project B, as it has not yet finished (hence the "null" in the stage 2 columns).

I think the logic of this would be a lookup which starts at the last "Stage X days over schedule column" and works leftwards until it finds a value that isn't null/a date. I maanged to do this in Excel using the formula: =LOOKUP(2,1/([RANGE OF CELLS WITH THE DAYS BEHIND SCHEDULE FOR EACH STAGE IN]),[RANGE OF CELLS WITH THE DAYS BEHIND SCHEDULE FOR EACH STAGE IN]).

I would really appreciate any guidance on how to do this in M Code!

Best,

H

[HRWhite]

Hi Xiaoxin, thanks for getting back to me. Would you be able to say a bit more on how I would convert to an unpivot table, and how I would lookup using the stage values. Significantly, the stage I am looking for is contingent on whether the stages after it have data or not--ie if the final stage, 5, has a value (e.g. 3 days' slippage), then the lookup should return 3. If stage 5 is blank, only then should it return the value of stage 4 (if stage 4 is not blank either). I am not sure how to write this in m code!

[Anonymous]

Hi @HRWhite,

Sure, here is the sample table that I transform from raw table structure based on M query functions, you can refer to the following codes:

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRMjDUNzDWNzICMU0QTKCoCRozVgeh0wkoYWSib2gEUWNkAGcamOoDkZExkJlXmpOjFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Stage 1 planned completion date" = _t, #"Stage 1 actual completion date" = _t, #"Stage 2 planned completion date" = _t, #"Stage 2 actual completion date" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Project"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByPositions({0, 8}, false), {"Stage", "Desc"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Project", type text}, {"Stage", type text}, {"Desc", type text},{"Value", type date}},"en-gb"),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Desc]), "Desc", "Value"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "over schedule", each Duration.Days([actual completion date]-[planned completion date]))
in
    #"Added Custom"

Functions used in above query:

Unpivot columns - Power Query | Microsoft Learn

Split columns by number of characters - Power Query | Microsoft Learn

Pivot columns - Power Query | Microsoft Learn

Duration.Days - PowerQuery M | Microsoft Learn

Regards,

Xiaoxin Sheng

[Anonymous]

HI @HRWhite,

I'd like to suggest you do unpivot column on your table to convert these repeated field to 'step, date, and value'. Then you can simply use existed step values as condition to lookup and return values.

Unpivot columns - Power Query | Microsoft Learn

Solved: Unpivot multiple columns - Microsoft Power BI Community

Regards,

Xiaoxin Sheng