Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Sure Pete.
INT_KEY | Project Name | DocumentNo | Rev |
366 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HDR-MEC-J709-DDT-008-04 | A.1 |
93 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HEN-CAI-J709-DS-**bleep**-TNA001-01 | A.2 |
132 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HEN-PRO-J709-HAZ-001-01 | B.1 |
137 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HMN-GEN-J709-SCH-001-01 | B.1 |
160 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HPR-QUA-J709-QCP-MS02A-06 | B.1 |
161 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HPR-QUA-J709-QCP-MS03A-23 | B.1 |
162 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HPR-QUA-J709-QCP-MS04A-09 | B.1 |
163 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HPR-QUA-J709-QCP-PS03A-01 | B.1 |
164 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HPR-QUA-J709-QCP-MS04A-08 | B.1 |
165 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HPR-QUA-J709-QCP-SS01A-01 | B.1 |
167 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HMN-DCT-J709-STD-001-01 | B.1 |
169 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-001-01 | - |
170 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-002-01 | - |
171 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-003-01 | - |
172 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-004-01 | - |
173 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-005-01 | - |
174 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-006-01 | - |
175 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-007-01 | - |
176 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-008-01 | - |
177 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-B02-009-01 | - |
178 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-001-01 | - |
179 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-002-01 | - |
180 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-003-01 | - |
181 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-004-01 | - |
182 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-005-02 | - |
183 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-006-01 | - |
184 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-007-01 | - |
185 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-008-01 | - |
186 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D01-009-01 | - |
187 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D34-001-01 | - |
188 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D34-002-01 | - |
189 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D34-003-01 | - |
190 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D34-004-01 | - |
191 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D34-005-01 | - |
192 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D34-006-01 | - |
193 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D34-007-01 | - |
194 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-J709-IS01A-D34-008-01 | - |
268 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HEN-CAI-J709-DS-AIT-TNA001-01 | 0.1 |
271 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HEN-CAI-J709-DS-LIT-TNA001-01 | 0.1 |
272 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HEN-CAI-J709-DS-LIT-TNK001-01 | 0.1 |
273 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HEN-CAI-J709-DS-LSL-TNK002-01 | 0.1 |
274 | 110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT | 110942-HEN-CAI-J709-DS-LSL-TNK003-01 | 0.1 |
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
Proud to be a Datanaut!
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:
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.
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
Proud to be a Datanaut!
Thanks Pete. This is way more efficient than my previous steps. Everything seems to be in order. I will have to do some checks but this looks great! Thanks for the help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.