Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |