Forum Discussion

Collan's avatar
Collan
New Member
1 year ago
Solved

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 ...
Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

    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!

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

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's avatar
Collan
New Member
1 year ago

Thank you so much Omid_Motamedise - it works purfectly!