Forum Discussion

Ian_XYZ's avatar
Ian_XYZ
Frequent Visitor
9 months ago
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...
Need Help
  • Ian_XYZ's avatar
    Ian_XYZ
    9 months ago

    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.

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
9 months ago

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = let
                 fx=(x)=>Text.Format("#[Location]|#[Product]#[Value]#[Description]",x&[Value=Number.Abs(x[Value])])
              in
                Table.Combine(
                              Table.Group(
                                          Source,
                                          {"Location","Product","Value","Description"},
                                          {"n",(tbl)=>Table.AddColumn(
                                                                      tbl,
                                                                      "Comments",
                                                                      (x)=>if Text.StartsWith(x[Customer],"P") and List.Select(tbl[Customer],each Text.StartsWith(_,"T"))<>{} then "Cleared" else null
                                                                     )
                                          },
                                          1,
                                          (x,y)=>Value.Compare(fx(x),fx(y))
                                         )[n]
                              )
in
    Custom1