Forum Discussion

Wigginsk's avatar
Wigginsk
Frequent Visitor
1 year ago

Power Query M return Max value

Hi,
I'm hoping someone can help with this. In power query M table below I need to be able to return the 3 rows highlighted in yellow for the WorkflowProcessedID 4362577.

I created the last 3 columns as conditional columns with the the following: - 
= Table.AddColumn(#"Added Column InterstateInfo", "QldInfo", each if [ActionEnd] = "Receive QLD Police Info" then 1 else 0)
= Table.AddColumn(#"Added Column QLDInfo", "OtherLegal", each if [ActionEnd] = "Assign for Legal Investigation" then 1 else 0)
= Table.AddColumn(#"Added Column OtherLegal", "SubInfo", each if [StatusName] = "Awaiting Submissions or Info" then 1 else 0)

What I now need to do is return only the rows that =1 (highlighted in yellow where they are the most recent. So for subinfo there are 5 row that equal 1 but i just need the most recent.

Any help would be much appreciated

 

 

 

Need Help

3 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Wigginsk 

     

    Please see the sample query below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZVbb+IwEIX/yognkColdpwE/BaxWwmpW0GR9qXqQwqGWksSNk7Yv7/jXO1yKbRF4smxv3NmjsfPzwPmBdQPw8HdAOaZkoU8CHjMCrkSMFOqFGuI0zUsRVoA6I8IdcjEoS71gHDX5XQC819Qr83zbCWUgp+448wPP3O7/8udxX8Q23gHkVL6jGmW7HeiEDBEWaNus8Wn3Btz368E6LVK8JGNq/nTOF83Z/yOd3IdFzJLYVjJGrX8iTPWdAoID7gX1u7rNWNXL7/f/i3+ke7VAtA+81FEX3+Dfx/LHfbuHf0y/0mssiTR9YNNlmsFCSzlNo2LMhctP3QIafiEe4wzD6KWH+33eXZArN5+ug2X+NG/GHekW1iWr4lUCn0owJNm6SaDnj/u8SGnrMZDb0Bg+loPlvx3fHK6/hVNby1f1XF4wXdI2AhwMXucTXr/TwJRKRTZZStfrgFqoG0IfcbxKsyNGvwthSoumblUg6syEDi0KwFjnBCrBzX/vsyLN5GbOq7J4FX+A4d0fJdx1/90Bs7cwQ8yYPH1LbD4t2bgqAc31wAnAfVO9OCTGbiKjzO4uohuI4CF5j340j3EZ2SfKRwjj6j/1CRv+Kzj+0EXAmsOYRdm6UHiFDWt3DKHE/3wyVRr2uZ6LhtzOKj5Y2R3YwAMA2f1f+B/8fADC7ernmBdciynkNqO6R/5fs2fcIYvcWjwUTrWuyp8G4ED5kFu67fB4JNT/hv29E2s/ljeh1FZjHo+rfme9t8+Q23/teAjI7Z/cq7+sxTjh6oXUaOhfQvXtn9S83EAEk5NvmVgMV/iiYXIN7EdIaP/L/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkflowProcessID = _t, StatusName = _t, StartDate = _t, ActionEnd = _t, QldInfo = _t, OtherLegal = _t, SubInfo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkflowProcessID", Int64.Type}, {"StatusName", type text}, {"StartDate", type datetime}, {"ActionEnd", type text}, {"QldInfo", Int64.Type}, {"OtherLegal", Int64.Type}, {"SubInfo", Int64.Type}}, "en-us"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Subinfo filter", each let 
tbl =  #"Changed Type", //name of the previous applied step
processID = [WorkflowProcessID],
selectedrows = Table.SelectRows (tbl, each [WorkflowProcessID] = processID and [SubInfo] = 1),
startDate = selectedrows[StartDate],
maxStartDate = List.Max(startDate)
in if maxStartDate = [StartDate] then 1 else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Qldinfo filter", each let 
tbl =  #"Changed Type", //name of the previous applied step
processID = [WorkflowProcessID],
selectedrows = Table.SelectRows (tbl, each [WorkflowProcessID] = processID and [QldInfo]= 1),
startDate = selectedrows[StartDate],
maxStartDate = List.Max(startDate)
in if maxStartDate = [StartDate] then 1 else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "OtherLegal filter", each let 
tbl =  #"Changed Type", //name of the previous applied step
processID = [WorkflowProcessID],
selectedrows = Table.SelectRows (tbl, each [WorkflowProcessID] = processID and [OtherLegal]= 1),
startDate = selectedrows[StartDate],
maxStartDate = List.Max(startDate)
in if maxStartDate = [StartDate] then 1 else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Combined Filter", each [Subinfo filter] ??[Qldinfo filter] ??[OtherLegal filter])
in
    #"Added Custom3"

    Warning: this can be a very slow one as it involves scanning a table. This should be done at the source or use DAX.

  • Wigginsk's avatar
    Wigginsk
    Frequent Visitor
    1 year ago

    Thanks so much for your response. 
    Sorry I'm fairly new to this so how do I add your code to my existing code? Below is my existing code for the query.

    let
    Source = Sql.Database("bcsrpt01.ccn.local", "ESS_Bluebox"),
    dbo_WorkflowProcess = Source{[Schema="dbo",Item="WorkflowProcess"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_WorkflowProcess, each [EndDate] >= RangeStart and [EndDate] < RangeEnd),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ParentWorkflowProcessID", "K2ProcessInstanceID", "ProcessFullName", "StartDate", "Priority", "AssignedToUser", "InProgress", "Renewal", "BatchID", "InHoldingStatus", "CurrentStatus", "PreviousCurrentOutcomeWorkflowProcessID", "IsMigrated", "PositiveIssuedDate", "Card(WorkflowProcessID)", "Card(WorkflowProcessID) 2", "HistoricApplicationStatus", "HistoricDocumentData", "InterfaceNewCardStaging", "LegalRFI", "ScheduleBatchWorkflowProcess", "SelfDisclosureInfo", "WorkflowProcessError", "dws.DWSApplication", "CurrentStatusWorkflowProcessHistoryID", "ApplicantID", "Applicant", "WorkflowProcessDetail"}),
    #"Filtered - Blue Card Application" = Table.SelectRows(#"Removed Columns", each [ProcessDescription] = "Blue Card Application"),
    #"Filtered - OutcomeTypeID" = Table.SelectRows(#"Filtered - Blue Card Application", each [OutcomeTypeID] = 3),
    #"Expanded WorkflowProcessHistory" = Table.ExpandTableColumn(#"Filtered - OutcomeTypeID", "WorkflowProcessHistory", {"StatusName", "StartDate", "ActionEnd", "RolledBack"}, {"StatusName", "StartDate", "ActionEnd", "RolledBack"}),
    #"Filtered - RolledBack" = Table.SelectRows(#"Expanded WorkflowProcessHistory", each [RolledBack] = null),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered - RolledBack",{"RolledBack"}),
    #"Added Column InterstateInfo" = Table.AddColumn(#"Removed Columns1", "InterstatInfo", each if [ActionEnd] = "Receive Interstate Police Info" then 1 else 0),
    #"Added Column QLDInfo" = Table.AddColumn(#"Added Column InterstateInfo", "QldInfo", each if [ActionEnd] = "Receive QLD Police Info" then 1 else 0),
    #"Added Column OtherLegal" = Table.AddColumn(#"Added Column QLDInfo", "OtherLegal", each if [ActionEnd] = "Assign for Legal Investigation" then 1 else 0),
    #"Added Column SubInfo" = Table.AddColumn(#"Added Column OtherLegal", "SubInfo", each if [StatusName] = "Awaiting Submissions or Info" then 1 else 0)
    in
    #"Added Column SubInfo"

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Wigginsk ,

       Based on the provided code from danextian, you can modify your code as shown below and paste it into your Advanced Editor:

      let
  Source = Sql.Database("bcsrpt01.ccn.local", "ESS_Bluebox"), 
  dbo_WorkflowProcess = Source{[Schema = "dbo", Item = "WorkflowProcess"]}[Data], 
  #"Filtered Rows" = Table.SelectRows(
    dbo_WorkflowProcess, 
    each [EndDate] >= RangeStart and [EndDate] < RangeEnd
  ), 
  #"Removed Columns" = Table.RemoveColumns(
    #"Filtered Rows", 
    {
      "ParentWorkflowProcessID", 
      "K2ProcessInstanceID", 
      "ProcessFullName", 
      "StartDate", 
      "Priority", 
      "AssignedToUser", 
      "InProgress", 
      "Renewal", 
      "BatchID", 
      "InHoldingStatus", 
      "CurrentStatus", 
      "PreviousCurrentOutcomeWorkflowProcessID", 
      "IsMigrated", 
      "PositiveIssuedDate", 
      "Card(WorkflowProcessID)", 
      "Card(WorkflowProcessID) 2", 
      "HistoricApplicationStatus", 
      "HistoricDocumentData", 
      "InterfaceNewCardStaging", 
      "LegalRFI", 
      "ScheduleBatchWorkflowProcess", 
      "SelfDisclosureInfo", 
      "WorkflowProcessError", 
      "dws.DWSApplication", 
      "CurrentStatusWorkflowProcessHistoryID", 
      "ApplicantID", 
      "Applicant", 
      "WorkflowProcessDetail"
    }
  ), 
  #"Filtered - Blue Card Application" = Table.SelectRows(
    #"Removed Columns", 
    each [ProcessDescription] = "Blue Card Application"
  ), 
  #"Filtered - OutcomeTypeID" = Table.SelectRows(
    #"Filtered - Blue Card Application", 
    each [OutcomeTypeID] = 3
  ), 
  #"Expanded WorkflowProcessHistory" = Table.ExpandTableColumn(
    #"Filtered - OutcomeTypeID", 
    "WorkflowProcessHistory", 
    {"StatusName", "StartDate", "ActionEnd", "RolledBack"}, 
    {"StatusName", "StartDate", "ActionEnd", "RolledBack"}
  ), 
  #"Filtered - RolledBack" = Table.SelectRows(
    #"Expanded WorkflowProcessHistory", 
    each [RolledBack] = null
  ), 
  #"Removed Columns1" = Table.RemoveColumns(#"Filtered - RolledBack", {"RolledBack"}), 
  #"Added Column InterstateInfo" = Table.AddColumn(
    #"Removed Columns1", 
    "InterstatInfo", 
    each if [ActionEnd] = "Receive Interstate Police Info" then 1 else 0
  ), 
  #"Added Column QLDInfo" = Table.AddColumn(
    #"Added Column InterstateInfo", 
    "QldInfo", 
    each if [ActionEnd] = "Receive QLD Police Info" then 1 else 0
  ), 
  #"Added Column OtherLegal" = Table.AddColumn(
    #"Added Column QLDInfo", 
    "OtherLegal", 
    each if [ActionEnd] = "Assign for Legal Investigation" then 1 else 0
  ), 
  #"Added Column SubInfo" = Table.AddColumn(
    #"Added Column OtherLegal", 
    "SubInfo", 
    each if [StatusName] = "Awaiting Submissions or Info" then 1 else 0
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Added Column SubInfo", 
    "Subinfo filter", 
    each 
      let
        tbl = #"Added Column SubInfo",  //name of the previous applied step
        processID = [ParentWorkflowProcessID], 
        selectedrows = Table.SelectRows(
          tbl, 
          each [ParentWorkflowProcessID] = processID and [SubInfo] = 1
        ), 
        startDate = selectedrows[StartDate], 
        maxStartDate = List.Max(startDate)
      in
        if maxStartDate = [StartDate] then 1 else null
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom", 
    "Qldinfo filter", 
    each 
      let
        tbl = #"Added Column SubInfo",  //name of the previous applied step
        processID = [ParentWorkflowProcessID], 
        selectedrows = Table.SelectRows(
          tbl, 
          each [ParentWorkflowProcessID] = processID and [QldInfo] = 1
        ), 
        startDate = selectedrows[StartDate], 
        maxStartDate = List.Max(startDate)
      in
        if maxStartDate = [StartDate] then 1 else null
  ), 
  #"Added Custom2" = Table.AddColumn(
    #"Added Custom1", 
    "OtherLegal filter", 
    each 
      let
        tbl = #"Added Column SubInfo",  //name of the previous applied step
        processID = [ParentWorkflowProcessID], 
        selectedrows = Table.SelectRows(
          tbl, 
          each [ParentWorkflowProcessID] = processID and [OtherLegal] = 1
        ), 
        startDate = selectedrows[StartDate], 
        maxStartDate = List.Max(startDate)
      in
        if maxStartDate = [StartDate] then 1 else null
  ), 
  #"Added Custom3" = Table.AddColumn(
    #"Added Custom2", 
    "Combined Filter", 
    each [Subinfo filter] ?? [Qldinfo filter] ?? [OtherLegal filter]
  )
in
  #"Added Custom3"

       

      Any comment or problem, please feel free to let me know.

      Best Regards