The "No" doesn't just disappear randomly. Sorting by supplier (ascending) and Yes/No (descending) will put a "Yes" row above a "No" row for those transactions where there are multiple hits. Removing duplicates where date AND supplier AND amount are the same, automatically keeps the "Yes" row as it will be the first one.

Riny:

Aha! I didn't know AND is prioritized before OR when using Distinct (wonder what happens if more than one Yes same SupplNr on different dates, all within agreement dates)

Is the SupplierNr match test done in first step of merged table?

(btw, do you know what to do when the button for merging is grey and not available no longer..is it still possible to write the merge code as script in advanced editor, and how do I insert of a new table in PQ to do that - seems I'm only able to insert a blank query..)

Well, if the dates are not the same, you'll end up with two "yes" rows.

The first step in the Merge query is indeed matching up the Suppliers as you can see from the first step in the AE. Note that I chose to do "Merge as new" so that a new query gets created in the first step like this:

Hi Riny,

Could you try Merge button in Power BI Query yourself, see attachment. I'm not able to choose Merge button there, it's all greyed out. Really stopping on this point..not getting further. Also I've changed the datatype for SupplNr to text, as its' imported in numbers initial.
-> What is possible with regards to merging in Excel Query view, seems _not_ possible in Power BI Query under Transform menu.

Could I alternatively write this code in a blank query (or an empty table, but can't find a choice for empty table in PQ, any tips to what allready tried..?)
Source = Table.NestedJoin(TableB, {"SupplierNr"}, TableA, {"SupplierNr"}, "TableA", JoinKind.FullOuter)

Trond

Your picture points to the Merge Columns button. As said, you need to select at least two columns for the button to become active.

Adding a blank query is under Get Data, From Other Sources. And yes, when you press the "Blank Query" button you can write the code directly in the formula bar, but leave out "Source". Note that this merges TableB and TableA, by SupplierNr. and that it has nothing to do with Merge Columns.

The easiest way would be to select the TableB query and press Merge Queries on the Home tab and then Merge Queries as New. Don't know the Norwegian on that, sorry. Added some screenshots to clarify.

Hi again,

Finally I understood not using the Merge Columns button (perhaps meaning Append for columns matching, witch is the opposite of merging rows). Found the correct button under Home and Merge Queries - thanks for advising me here!

However, still 2 failures in output regarding Yes or No under Contract column:
1) SupplNr 227 showing No in my file not Yes, after duplicate removal (in your Excel file it showed Yes, witch is correct)
2) SupplNr 330 both say Yes and No at same transaction date with difference amounts, witch is impossible (I did see the fail in your merged file first tonight)

Btw, removed relations in .pbix after failure 227 and 330 not allowing me to save the file otherwise.

Trond

Hi Trond,

Opened the pbix file but can't really use it as it links to sources on you local disk. But I did look at the M-code and notice that you forgot to wrap the sorting step in the "Merged" query in Table.Buffer ( ...... ). Kindly refer to the file I sent you earlier.

With regard to supplier 330, I don't follow. It has two different dates AND different amounts. One with a contract and one without.

Best regards,

Riny

Hi Riny,

You are right about SupplNr 330, I just didn’t see the year different, as day and month were the same.

Now I’ve taken your steps over i my larger production files. However a new problem have arraised: If there is more then one valide FromDate-ToDate agreement for the same SupplNr with regards to in between TransactionDate, there is written more then one row of Yes in the custom column. And then I’m not able to filter out just one Yes by removing duplicates afterwards. What more to do (in what order)? 

Thank you so far,
Trond

Again, it's a bit difficult to follow. I worked off your simplified example and concluded that if the date AND the supplier AND the amount are the same that these are duplicated and that only the first one should remain. So, any row where these three element are not the same are NOT duplicates and thus will have a row with either yes or now in the final table.

If this makes no sense, please upload a realistic data set that includes all of the possible situations and clearly explain what the outcome should be and why.

Hi Trond,

Sorry to say, but your suggestion neither solves the question regarding matching SupplNr, nor the problem with nested loop for TransactionDate being between DateFrom and DateTo.

Trond

What I suggested does solve matching SupplNr, that's what a merge does. The result of the merge is a column with tables.
If you need to filter out the tables generated into the merged column, you can do that with a new column:
= Table.AddColumn(Source, "FilterRows", each Table.SelectRows([TableA], (x)=> x[TransactionDate] >= _[DateFrom] and x[TransactionDate] <= _[DateTo]))

Then add the column I indicated with this formula:

=if Table.RowCount([FilterRows])>0 then "Yes" else "No"

Final Query:

Hi Catalin,

To your last response, see attachment. I added two steps after your code, in order to have amount in table.

There is still something wrong with SupplNr 227 and 330 (compare Merged table with table A transaction dates/amounts/"Checked" column).

Trond

Hi Riny,

What I mostly try to illustrate in last post to you, is still some inconsisty in the earlier Excel file you made (merged) to me.

See attached, I've added 2 rows in table A, with no changes in table B.

Updated merge in PQ, an as you see the second transaction for SupplNr 227 disappears, because of duplicate control with same amount 450 on same date.

However, a working solution should also accept several transactions same day with same mount. Let's focus on these small tables for now..

Trond

I believe that you have exhausted my creativity by adding yet another hypothetical situation. If it is indeed possible that a supplier has multiple transactions on the same date AND for the same amount, then obviously none of my earlier solutions will work. But then you need to introduce another piece of information, being a unique transaction code. How else can you or Excel determine that we deal with a valid transaction or an accidental duplication.

To your last response, see attachment. I added two steps after your code, in order to have amount in table.

There is still something wrong with SupplNr 227 and 330 (compare Merged table with table A transaction dates/amounts/"Checked" column).

 Power BI solution cannot be used:

DataSource.Error: Could not find a part of the path 'C:UsersHjemDownloadsinput-tables-A-and-B-3(final edition).xlsx'.

Also, please don't just say "there is something wrong". Explain what is wrong, we are using our time to help you, but searching for "something" is not fun.

When you expand a column with tables, if one of those tables has more than one row, it will obviously add another row in the original table. As you mentioned yourself, it is possible to have multiple contracts in a specific period, this translates into tables with more than 1 row into TableA tables.

You can change the Check step to count the rows, most probably SupplNr 227 and 330 have more that 1 row:
Check = Table.AddColumn(FilterRows, "Check", each Table.RowCount([FilterRows]))

You have to decide what amount you want to bring, knowing that there can be 3 cases: empty tables, 1 row tables, multirow tables in TableA.

Instead of expanding, which creates duplicate rows, you can bring the sum of all amounts in a new column:

Amounts = Table.AddColumn(Check, "Amount", each List.Sum([FilterRows][Amounts]))

or the first one:

Amounts = Table.AddColumn(Check, "Amount", each try [FilterRows][Amounts]{0} otherwise 0) ' error check for empty tables

Hi Riny,

To post 28; this is why I about two weeks ago tried to expand my test data set a bit. Else you are probably right about using another uniqe column in order to deal better with duplications. I will follow that advice.

I have just one last question here, before we could close this thread;

When you made the duplication removal in Excel PQ, where Yes automatically was kept and No lines removed (because Yes is connected to the logical operator AND?), what failure can there be when No is kept instead in duplication removal and Yes is removed - perhaps possible to explicit define in code Yes being kept before No, if two rows with columns else are identical? (this happens in my production file when using the duplicate removal)

Thanks again for helping me out, sorry a bit many questions too.