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...
Need Help
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