Forum Discussion

CorneGeyser's avatar
CorneGeyser
Helper I
6 years ago
Solved

Find MAX based on two criteria

Hi All,

 

I have set up a duplicate filter in Power Query to find the latest revision of a document from the system. I did this by creating a copy of my original table and using the group function to group these document names by the latest/max revision number that is created by the system.

 

This worked well. However, I came across an issue where a previous revision of a document would be updated on the system and this transaction will then get the latest system revision number instead of the actual latest revision of the document.

 

From the image above, I use the 'INT_KEY' column to find the latest revision. The 'Rev' column is what indicates the actual document revision as per company standards.

 

What I Need:

 

if there is a 0.1 revision in the 'Rev' column for a document, it has to keep that line for a document. If there is no 0.1 for a document, it must continue using my grouping method or something similar/better.

Need Help
  • BA_Pete's avatar
    BA_Pete
    6 years ago

    Hi CorneGeyser ,

     

    Apologies for the delay.

    Please try this code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZzdkppKEMdfhdrrcHZARM3dCLPKCV81jK5JKu9w3v/qDKgoMNPMuESbKkKyaoRfTff0vz/Y37/fvLdvb55HNoHv7mPulrxw/12RjVsmsUuI55L6A/Sf+lxweXLlnw/GeRI51SEt91QwJyryI+NVUuTyTRK+E//dJz5xCPneHPWr3rs8vA0xeFUe8jbqcxLXVyu4k+TynFFxvsTI8efb7zcfxPKNsCpRcLpjDs1jJy6qJN+hwVuAeAsl3v5nzIsdy52S8eKUxAwbVABCBWeouMapGItfQFVftrmwAczSzK9qO/2MXuJXe/qrKOU1sozlojKjCs3cCqR6jls9Qrcy86oO3TPt7xGmtYlT9ZiapTrUi0ajBNsibcwcq94hXxSwklwwntP0CmbG5REz3wLBnuNbDwLCUmOhBHymez2IBUuNQIn1NA97kAnWFx0nM4tenjcxGGcRS44sdj54kTkPYsKKwzfCVLocTlxYkyyUuIAD4oSEJUqghNS5I07Ce5nCcpel0ZkwTSphnLOVKc2FkxXSep36P8qP+FPQyruR58s39gGdK78h6XqU1DckfQqeIdVmlOq2ftptZw7r55NRUt+QtMYLvorXSEx7wCqqg0H7I6uIFw1ZvS5rxi6sceXSnd4vs+Qk9xpH0PyHQ3eJoKJ5y59CCMh7kOe4ethUfV8P9UFzLVQV8cN2y7gjP4QHZqGH2QM75764uBcOikBPUUaplkJwmlfStZzykJV4aJZjNOpSXWtguGhCgCbT11MBjYUEbDUGplkmja5CQrUeo1IXUvsBChvWZgwrmCPWoicmIprcAmwiXJFTnX/9t3cyJq85FYu89BdZPD1LCrOk7MhSp9nNs0SggvLHoH7MEGoBQFXpGUq9+Z2hqs9ERHs8PIEBj3rbw8mz1POUsCeVnFXVgTOcdhcCXPxYb+S6dWq5OEsT9uEcaXpkeMBWBmDqAIUcbK0HE7AlCpZJ5UcFWmPc6NFOR4iMRuIgtUSMbK0Cay3RVifutcQaBYu1lmhZXhF2LUtIgbWomBWdtbro0T0tGtuCWcuMuYBZ640W7EV6wxbQWngMAY3is796JwtD7g/xAGNdtm0ameejrIhPh7jWcmTeuNYipcV9nUixtWBbtdIiPl2tWJItrWRLHTmVJRAUsmVpJVs6LMrAjgPKSq3MBcpKpCigbrEcBw/QQVF06trdQdepmwIKqI7a7hGAQBkqrxmsFtBTUbQgbyMdvRYkslUCGiqKXuRtGufai0SGA8iKoSruGJ1OFeMwPqiVMmy23rRSv9mKa7lCMoalzjkHXVdkWICkGIrbjhUC4haFIYbAIIais2wyvYdt8YDpDEWLeXR0DxseUBAZJCUd0xwmJTgsEprcGDbPtdNdaBdsdJZDXRWYDZ+u8AFOkSZ5JfihuVw6oRI5W+OXpipDQIKoc2NVSV+iGFdpnpDwh0ApQ50kg9VvZHQroJyhzpZnRQcVOJRps64Ejg0MKnIoM8y5gAGFDnUWM1bbxwYIiBC1QDao/mJjBOofSqGlr/5iI9N1ZsDnBoYRe0KsaR4TWJk+0NLRxQYPREjMr5NO8MTHqqdNyqT86jDPJGjyPnS2aQi2MQCzG+bBAbYmBmB2XVCy/EtgnB0T9mnqbGvPgMyu4YmFTKdLLtuI2sGSot0tpsBQ74tnEEOMngoBd0Obx8Mm4VPvhpYLBciQfqq2HUxy18/ZFryUf9P056+zBAmnDdQP/faSNdR4gamU2QwKJmAYpJ+hzYYJmPjo52VKplv6ggIHqHv0s7E54AAVj34OttUoCoRGtwFKHb2spEc1zEpQ8OiKG90gtR2rHk4Ic/nGx3CAkoYAjQ7Ii1GsE1DSUGf8pwtYRPOIpWkT09UZoxe8k5Xhq6cuy/2Xt8dJ8a+WQycZVNrulvu22s4jf1nbmWqfzZ1KaACyioTU3dYR57oQzfXoFcIj5Nprdnb1RJ83Rf7OTrXcPja/Te3I8lgaV0wFVdI0z7+v2h+bOx6ChQCYD4P5qMFGGiXq2HpnelMktB3Ti2T21RRZzJOKDfCoa2fmqYehmXmahOmul/UIETCa0SEiRkT1bh00RG/3s7r9d5rJ3LsbrzflqGjCWmS+K3uEGN78tlvzevnN//kf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INT_KEY = _t, DocumentNo = _t, Rev = _t, STS = _t, StsRcvd = _t, Title = _t, Received = _t, Schedule = _t, Forecast = _t, Authorised = _t, Discipline = _t, Type = _t, Reason = _t, Remarks = _t, CLOSED = _t, EPO = _t, EPO1 = _t, EPO2 = _t, PO3 = _t, PACKAGE1 = _t, PACKAGE2 = _t, PACKAGE3 = _t, PACKAGE4 = _t, PM = _t]),
    addProjectName = Table.AddColumn(Source, "Project Name", each "110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT"),
    groupDocNoProject = Table.Group(addProjectName, {"DocumentNo", "Project Name"}, {{"data", each _, type table [INT_KEY=text, DocumentNo=text, Rev=text, STS=text, StsRcvd=text, Title=text, Received=text, Schedule=text, Forecast=text, Authorised=text, Discipline=text, Type=text, Reason=text, Remarks=text, CLOSED=text, EPO=text, EPO1=text, EPO2=text, PO3=text, PACKAGE1=text, PACKAGE2=text, PACKAGE3=text, PACKAGE4=text, PM=text, Project Name=text]}}),
    addMaxCalc = Table.AddColumn(groupDocNoProject, "max", each if [data][Rev] <> "0.1" then Table.Max([data], "INT_KEY") else null),
    expandMaxCalc = Table.ExpandRecordColumn(addMaxCalc, "max", {"INT_KEY", "Rev"}, {"INT_KEY", "Rev"}),
    remDataCol = Table.RemoveColumns(expandMaxCalc,{"data"})
in
    remDataCol

     

     

    The output looks correct to me, but I think there was only one example of a "0.1" report in the sample dataset, so difficult for me to confirm that it is working correctly.

    Adjust the expandMaxCalc step to bring in more of your fields for testing.

     

    Pete

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    6 years ago

    Hi CorneGeyser ,

     

    Would it be possible to share just that visible page of data in table format please? It appears as though your [DocumentNo] naming convention is very well implemented, so I think there may be simple way to do this using that field alone, but I'd like to test it first.

     

    Pete

    • CorneGeyser's avatar
      CorneGeyser
      Helper I
      6 years ago

      Sure Pete.

       

      INT_KEYProject NameDocumentNoRev
      366110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HDR-MEC-J709-DDT-008-04A.1
      93110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-**bleep**-TNA001-01A.2
      132110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-PRO-J709-HAZ-001-01B.1
      137110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HMN-GEN-J709-SCH-001-01B.1
      160110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-MS02A-06B.1
      161110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-MS03A-23B.1
      162110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-MS04A-09B.1
      163110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-PS03A-01B.1
      164110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-MS04A-08B.1
      165110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-SS01A-01B.1
      167110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HMN-DCT-J709-STD-001-01B.1
      169110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-001-01-
      170110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-002-01-
      171110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-003-01-
      172110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-004-01-
      173110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-005-01-
      174110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-006-01-
      175110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-007-01-
      176110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-008-01-
      177110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-009-01-
      178110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-001-01-
      179110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-002-01-
      180110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-003-01-
      181110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-004-01-
      182110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-005-02-
      183110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-006-01-
      184110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-007-01-
      185110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-008-01-
      186110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-009-01-
      187110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-001-01-
      188110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-002-01-
      189110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-003-01-
      190110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-004-01-
      191110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-005-01-
      192110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-006-01-
      193110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-007-01-
      194110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-008-01-
      268110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-AIT-TNA001-010.1
      271110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-LIT-TNA001-010.1
      272110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-LIT-TNK001-010.1
      273110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-LSL-TNK002-010.1
      274110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-LSL-TNK003-010.1
      • BA_Pete's avatar
        BA_Pete
        Super User
        6 years ago

        Hi CorneGeyser ,

         

        I'm hoping I've understood your requirements correctly, but you will need to check.

         

        In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZc9b4MwEIb/SsSI4sofBOzRAVpo+QqQpVGWStk69P9PpXbU5HA63XVh4p6Xk++RudMpUmkabSMhuEkke824YVPf1k2/afrj8nwux7HON9OxGSo7l5uhsd18K6iKkbVl7guLYmaca8aT5QX7JKLz9hQZhcGXHcttfcVPLI4/Pi+Xrzhmc2c5F4wLFyVdlFASmTWMvS+s7Dv75e+vrQiVYfhtx16WDF+YVw/4Kcfwh5EdjtYXHvKBtROXlvEUJAjiBGWZVCABdQYPEpKlBwMSUBO1ThhcD6tzSP6hBw0SdpQJ08RF0AN2Vot8vhbOxaNZNQi+K6jdV++5vNGZZ2cYDwK2hGyMAQFbQTZm9gN2AtmYqQ/YO8jGzHvATiEbM+kBO4NszNUVsDVkY/wJ2AayNQ27+PFm7Q6Rl54N3dFEXno2dEcTeenZ0B1N5KVnL+7IOzaRl54N3dFEXno2dEcTeenZ0B1N5KVnQ3c0kZeFSgJ3NJWXjr1yh8pLx4buGCovHRu6Y6i8dGx47xgqLx0buoPaOwI2dMdQeenYwB2ZYmZwvS/ZegabEr/+vUnUX9A6pfkzBbuPhSlvD1IoN8xmanyKXKegdoK/UtRdyvkb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INT_KEY = _t, #"Project Name" = _t, DocumentNo = _t, Rev = _t]),
    dupeDocNo = Table.DuplicateColumn(Source, "DocumentNo", "docNo"),
    splitEndDigits = Table.SplitColumn(dupeDocNo, "docNo", Splitter.SplitTextByPositions({0, 6}, true), {"Document", "Revision"}),
    groupDocument = Table.Group(splitEndDigits, {"Document"}, {{"data", each _, type table [INT_KEY=number, Project Name=text, DocumentNo=text, Rev=text, Document=text, Revision=text]}}),
    addMaxRevision = Table.AddColumn(groupDocument, "max", each Table.Max([data], "Revision")),
    expandMaxRevision = Table.ExpandRecordColumn(addMaxRevision, "max", {"INT_KEY", "Project Name", "DocumentNo", "Rev", "Revision"}, {"INT_KEY", "Project Name", "DocumentNo", "Rev", "Revision"}),
    remOthCols = Table.RemoveColumns(expandMaxRevision,{"Document", "data", "Revision"})
in
    remOthCols

         

         Summary:

        1) Split last 6 digits from [DocumentNo] as these appeared to be revision numbers independent of the [Rev] field, and called these two new fields [Document] and [Revision].

        2) Grouped table by [Document].

        3) Retained rows where [Revision] was the max in the [Document] group.

        4) Expanded rest of the table back out and removed created fields.

         

        Pete