Searching a row value in the same column same table using power query.

Question

i have something to achieve via logic but unable to achieve please folks help me on this how to achieve this.

Below is the data sets: -

Condition: -

If same check number is in the same table with opposite sign in the same column then it is consider reconcilied.
if same check number is in the another table with same sign in the same column then it is consider reconcilied.

Please help me to build the power query using the above condition.

Answer

Here's one way you could determine reconciled status for the two cases you described above.

Select Table1's Amount1 column. Click the Add Column ribbon tab, then Standard -> Multiply. Enter -1 to get the negated version of each number.
Do a self-merge of Table1 against itself, matching on Amount1 and the negated Amount1.
Any rows that have a single row in the merge column are reconciled.

You can do a similar self-merge for the different table case, without negating the numbers first.

EDIT: Here's some M code that demonstrates a possible way to do this for the same-table case.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Check Number", Int64.Type}, {"Amount 1", Int64.Type}, {"Amount 2", Int64.Type}}),
    #"Inserted Multiplication" = Table.AddColumn(#"Changed Type", "Multiplication", each if [Amount 1] = 0 then null else [Amount 1] * -1, type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Neg Amount 1"}}),
    #"Inserted Multiplication1" = Table.AddColumn(#"Renamed Columns", "Multiplication", each if [Amount 2] = 0 then null else [Amount 2] * -1, type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Multiplication1",{{"Multiplication", "Neg Amount 2"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns1", {"Amount 1"}, #"Renamed Columns1", {"Neg Amount 1"}, "Renamed Columns1", JoinKind.LeftOuter),
    #"Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns1", {"Check Number"}, {"Renamed Columns1.Check Number"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Renamed Columns1", {"Amount 2"}, #"Expanded Renamed Columns1", {"Neg Amount 2"}, "Expanded Renamed Columns1", JoinKind.LeftOuter),
    #"Expanded Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Renamed Columns1", {"Check Number"}, {"Expanded Renamed Columns1.Check Number"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Expanded Renamed Columns1",{"Renamed Columns1.Check Number"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Renamed Columns1.Check Number] <> null),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Duplicates",{{"Renamed Columns1.Check Number", "Reconciled Check Numbers"}})
in
    #"Renamed Columns2"

Searching a row value in the same column same table using power query.

1 answer