Forum Discussion
Validation of data against other rows PowerQuery
Hello All - hoping that someone can assist with guidance on M Code to achieve the Validation Result shown
The business scenario is that a set of manually entered input data needs to be validated before upload into an ITSM platform.
Columns A/B/C are data from the existing transformation steps & we need to create the Validation Result (Column D) as a new column (for further processing). Note that no rows can be removed during this validation.
| Device | Support Contract | Class | Validation Result |
| Server1 | ContractA | Server | OK |
| Server1 | ContractB | Server | OK |
| Server2 | ContractA | Server | Fail |
| Server2 | ContractC | Network | Fail |
| Server3 | ContractA | Server | OK |
| Server4 | ContractB | Server | OK |
| Router1 | ContractC | Network | OK |
A Device may have 1 or more Support Contracts assigned - these Contracts are used to define the associated Class of device.
When multiple Contracts are assigned to a specific Device, we need to check that each of associated Classes are the same (and mark both records as a Fail if the Class values are different)
We've already tried various GroupBy options but have been unable to find a workable result
Thank you in advance
Just copy and the below code and paste it into the Advance editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstMlTSUXLOzyspSkwucQSyIaJKsTrYFDhhU2BEyARkBc5Atl9qSXl+UTaSCmNCRpjgdkRQfmkJqitR7IgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Device = _t, #"Support Contract" = _t, Class = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Device", type text}, {"Support Contract", type text}, {"Class", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Device"}, {{"Count", each [a=_,b=List.Count(List.Distinct(a[Class])),c=Table.AddColumn(a,"Validation", (x)=> if b=1 then "Ok" else "Fail")][c]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Device"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Device", "Support Contract", "Class", "Validation"}, {"Device", "Support Contract", "Class", "Validation"}) in #"Expanded Count"If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!
3 Replies
Just copy and the below code and paste it into the Advance editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstMlTSUXLOzyspSkwucQSyIaJKsTrYFDhhU2BEyARkBc5Atl9qSXl+UTaSCmNCRpjgdkRQfmkJqitR7IgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Device = _t, #"Support Contract" = _t, Class = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Device", type text}, {"Support Contract", type text}, {"Class", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Device"}, {{"Count", each [a=_,b=List.Count(List.Distinct(a[Class])),c=Table.AddColumn(a,"Validation", (x)=> if b=1 then "Ok" else "Fail")][c]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Device"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Device", "Support Contract", "Class", "Validation"}, {"Device", "Support Contract", "Class", "Validation"}) in #"Expanded Count"If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!
Thank you so much Omid_Motamedise - it works purfectly!
You are welcome
