Forum Discussion
Solved
Identify offsetting entries based on multiple criteria
Hi there. I have a data set in which a subset are opposing entries but to different "customers" (example data below). I need to identify in my data set which rows have the following criteria: Custo...
Thanks Dinesh. I've been pulled in a few different directions so won't be able to finalize for another week or so. Happy to mark it as done given I should be able to sort something and appreciate the support everyones provided.
Hi Ian_XYZ, another solution:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZHNCoMwEIRfRXJWyE99AOu5KLQ38RA0DaEhkaUF+/Y1xkpaQ6mngSUz386maVDJtbpaMIqjFBXDoMWkF5JjJ/DobpOSpOdPp9hNj3ZMKugFoDZt0EmMqrPTuAJu5GymODTTxeynZ2WkFoG/ACnMXRmHrwWHOSCPBbD/6P5ZOSe9F/eBG3a0e40xWQPYEpDFqn/Y190DPNtdfYF/Hz6LdY//3CHg01/b76Bv7O0L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Product = _t, Customer = _t, #"Shipping method" = _t, #"Shipping Time" = _t, Value = _t, Description = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}}),
CheckCols = List.Buffer({"Location", "Product", "Description", "Value"}),
FilteredP001 = Table.SelectRows(ChangedType, each ([Customer] = "P001")),
Ad_AbsValue = Table.AddColumn(FilteredP001, "Abs Value", each Number.Abs([Value]), type number),
MergedQueries1 = Table.NestedJoin(Ad_AbsValue, {"Location", "Product", "Description", "Abs Value"}, ChangedType, CheckCols, "Merged", JoinKind.LeftOuter),
MatchedRows = Table.SelectColumns(Table.SelectRows(MergedQueries1, each not Table.IsEmpty([Merged])), CheckCols),
MergedQueries2 = Table.NestedJoin(ChangedType, CheckCols, MatchedRows, CheckCols, "Merged", JoinKind.LeftOuter),
Ad_Comments = Table.RemoveColumns(Table.AddColumn(MergedQueries2, "Comments", each if [Customer] = "P001" and not Table.IsEmpty([Merged]) then "Cleared" else null, type text), {"Merged"})
in
Ad_Comments