Validation of data against other rows PowerQuery

[Collan]

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

[Omid_Motamedise · Accepted solution]

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!

[Collan]

Thank you so much Omid_Motamedise - it works purfectly! 

[Omid_Motamedise]

You are welcome