Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Caggers08
Regular Visitor

Help with calculation / Flagging remediation issues

If I have an exception report to flag when some fields do not match based on a Group category i.e.

 

AccName  Acc Num  Group  Value

Bob           123              A       20

Jane          124              B        25

Bill            127              A        27

 

If the value differs on the 'Group' I would like them to flag to be looked at.

 

In the above scenario we would need to flag bob & bill as the values differ but the group is the same

 

However if we had the below

AccName  Acc Num  Group  Value

Bob           123              A       20

Jane          124              B        25

Bill            127              A        20

 

There would be no flag as it is working correctly. 

 

How would I do this in PowerBI?

 

Thankyou

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRMjQyBpKOQGxkoBSrE63klZiXChY3AZJOIHFTsLhTZk4OWNwcpt5cKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccName = _t, #"Acc Num" = _t, Group = _t, Value = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Group"}, {{"Count Values", each List.Count(List.Distinct(_[Value])), Int64.Type}, {"Rows", each _, type table [AccName=nullable text, Acc Num=nullable text, Group=nullable text, Value=nullable text]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count Values] = 2)),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Filtered Rows", "Rows", {"AccName", "Acc Num", "Value"}, {"Rows.AccName", "Rows.Acc Num", "Rows.Value"})
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRMjQyBpKOQGxkoBSrE63klZiXChY3AZJOIHFTsLhTZk4OWNwcpt5cKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccName = _t, #"Acc Num" = _t, Group = _t, Value = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Group"}, {{"Count Values", each List.Count(List.Distinct(_[Value])), Int64.Type}, {"Rows", each _, type table [AccName=nullable text, Acc Num=nullable text, Group=nullable text, Value=nullable text]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count Values] = 2)),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Filtered Rows", "Rows", {"AccName", "Acc Num", "Value"}, {"Rows.AccName", "Rows.Acc Num", "Rows.Value"})
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.