Forum Discussion
Solved
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 ...
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.
lbendlin
Super User
Super Userlet
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.