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

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

CorneGeyser's avatar
CorneGeyser
Helper I
6 years ago

Thanks for the assistance Pete. However, the DocumentNo column does not contain a document revision. The numbers that you are extracting are sheet numbers.

 

Below is a link to a sample document for more information:

 

Sample document 

 

The source table has 100 line items. Items 98 - 100 is the same document but they are all different revisions. If you follow my query steps as on this sample document, the document with 'Rev' as 'B.2' will be the latest revision. However in this case the actual latest revision is actually '0.1'. '0.1' should always be the latest revision.