Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Conditional lookup in specific order with M Code

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:

 

ProjectStage 1 planned completion dateStage 1 actual completion dateStage 1 days over schedule (calculated column which)Stage 2 planned completion dateStage 2 actual completion dateStage 2 days over schedule (calculated column which)
Project A01/03/2204/03/22301/04/2201/04/220
Project B24/12/2220/12/22-405/05/23nullnull

 

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

 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous,

      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