question

VijayYadav-2608 avatar image
0 Votes"
VijayYadav-2608 asked Ehren edited

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

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: -


118295-image.png


118334-image.png



Condition: -
1. If same check number is in the same table with opposite sign in the same column then it is consider reconcilied.
2. 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.

power-query-not-supportedoffice-scripts-excel-dev
image.png (3.4 KiB)
image.png (3.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@VijayYadav-2608

Please update your case with the expected result(s). Upload picture(s) showing the expected result(s) with the 2 Tables you exposed. Thanks

0 Votes 0 ·

1 Answer

Ehren avatar image
0 Votes"
Ehren answered Ehren edited

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"
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.