Log In
Register
Home
June 22, 2019
Offline
Hi folks!
I have two tables in Power BI, where on is the facts table with transactions (A), and the other being dimension table (B) with supplier contract info.
Table A consist of several columns, like TransactionDate, SupplierNr and more.
Table B consist of a few columns, like SupplierNr, DateFrom and DateTo. B is my contract table.
One column in table B is also showing the table-function, if possible to use Table.Selectedrows in order to loop through each rows while testing conditions.
Question:
How to add a new column in table A, witch tests and write «yes» or «no» for each matching SupplierNr, with regards to each TransactionDate in table A in between of DateFrom and DateTo in table B?
Not all SupplierNr are in table B. For speed its not necessary then to test every TransactionDate being in between DateFrom and DateTo, if SupplierNr is only in table A, and not always in table B.
There can also be more than one row for each SupplierNr in table B, as several contracts with same SupplierNr have different dates from and to.
In each new row in table A either «yes» or «no» should be written when refreshing table A. Where «yes» means contract exist to the SupplierNr and TransactionDate in contract period.
When no identic SupplierNr in table B, row in table A should just say «no», continuing the testing of SupplierNr (and TransactionDate) in next row of tableA.
In Power BI Report I can then filter on «yes» and «no» from table A after updating.
Only missing this in order to reach my goal with the Power BI report.
Thanx in advance, for anyone helping me out here!
Cheers, Trond
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Trond,
You can merge those 2 tables using as key the Supplier Nr.
In the new merge column, you will have tables, with 0 or more rows, depending on how many contracts are in table B for that SupplierNr.
Add a new column named "Has Contracts" with a simple formula to check if the number of rows in that merge column is greater than 0:
=if Table.RowCount([Merged])>0 then "Yes" else "No"
June 22, 2019
Offline
Hi Catalin,
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
Moderators
January 31, 2022
Offline
Perhaps the attached (updated) file contains the solution you asked for.
June 22, 2019
Offline
Hi Riny van Eekelen,
You have obviously done something workable in the attached file.. I'll try following applaid steps.. But how does the function "earliest transaction" work?
I have an extended question too: What if a column of Product categories with numbers from 1000-9999 are added to the transaction table A at bookkeeping (just one number each line of course). One subgroup can be 1005 'car repairs', another 2003 'travel', a third 3404 'bying cars' etc. The earlier total in transaction table A are then split into one or more lines when bookkeeping. In contract table B these subgroups should be listet at each contract line in a common cell, example like: '2003, 3004, 5007' for a specific contract with from and to date. In this example then Yes should be written only for the subgroup 2003 travel, meaning suppliers & transaction date & product category being true at same time - else No should be written. If possible this actually can make me smile! 
- last question above is just looking a bit into the future in Power BI..witch could be the ultimate analyse report for uncovering breach of contracts with suppliers, witch often are contracts made not just with dates from and to pr supplier, but also pr Product category (in my country at least).
Trond
June 22, 2019
Offline
Riny:
Btw, getting some error when copying the sentence in Merged table (Source step). The table that are produced in a column, has null in all its columns, when clicking on it. Probably meaning the two tables (A and B in comparison) doesn't connect in my production model.
I'm in Power Query now. Perhaps the failure is if not possible to merge a merged table with the transaction table? (as I’m not able to press the button option to merge two tables).
Trond
Moderators
January 31, 2022
Offline
Hi Trond,
The "earliest_transaction" serves as a variable used to filter out contracts from Table B (before merging) that have an expiry date before the earliest transaction date in Table A. So, it's not a function!
You can see in the Applied Steps how I find the earliest date with List.Min( ). Or actually, just by clicking on the TransactionDate column and then on the Transfer tab, Date, Earliest.
With regard to your extended question, it's difficult to visualise exactly what you need. Perhaps you can upload a file with a representative example of the data you are working with or envisage to be working with in the future.
With regard to the error message, make sure that the name of the previous step is spelled correctly. And if the error persists, you can upload a file producing the error. Much easier to help that way.
Best,
R
June 22, 2019
Offline
Hi Riny,
Okey, lets focus in two steps: 1) getting the existing file to work as intended, and 2) consider an extension regarding Product categories (not pri right now).
Attached is the file you made for me, with one minor change in table A (orange marked), in order to move the earliest_transaction filter "aside" when testing.
As the screendump shows for SupplierNr 227, this can't be both Yes and No at same time. The result output should just show SupplierNr 227 one time for the amount 450. In this case the Yes line is correct, as a contract exist in the actual period for SupplierNr 227, as more than one contract periode exist in table B.
Trond
Moderators
January 31, 2022
Offline
You didn't attach a file. Please try again and don't forget to press the Upload button once you added a file.
June 22, 2019
Offline
Hi Riny,
Files attached (thought I did that last post but
Trond
June 22, 2019
Offline
Hi Riny and others,
I also have another suggestion/or question to solution, using iterations (looping). See Yes-or-No-code-testing-OrganizationNr-missing-test-of-TransactionDate-in-between-of-DateFrom-and-DateTo-1.PNGImage Enlarger
new & better Power BI AGREEMENTS-List-DateFrom.PNGImage Enlarger
files attached!
Each row in TRANSACTIONS[OrganisationNr] isAGREEMENTS-List-DateTo-1.PNGImage Enlarger
AGREEMENTS-List-DateFrom-1.PNGImage Enlarger
first tested if match to AGREEMENTS[OrganisationNr]. My file allready does this now, see code in last step of TRANSACTION table in PQ (Power Query).
Btw, I made the PQ file by importing "Bying" and "Rentals" tables first, then making references (ref) of those tables, before merging these last two into a common AGREEMENTS table. The AGREEMENTS table is what should be tested against the TRANSACTIONS table for OrganizationNr and TransactionDate.
See the 4 steps in table "AGREEMENTS" in PQ, and specially the columms with formula making List of DateFrom and DateTo. Some of these OrganizationNr actually have more than 1 rows in Lists, making List in theory ideal for looping through each OrganizationNr, as OrganizationNr now is grouped avoiding any duplicates in AGREEMENTS table
Also see very last step in "TRANSACTION" table, some code I found on internet and tweeked bit, for doing a lookup test each OrganizationNr in TRANSACTIONS table if existing iAGREEMENTS-List-DateTo.PNGImage Enlarger
n AGREEMENTS table too.
My question is then finally:
Can someone help making the code for iterations in the very last step of TRANSACTION table, testing each List row in AGREEMENTS table of DateFrom and DateTo columns, by checking every TransactionDate if in between of those List AGREEMENT dates?
I have a good feeling function List.Generate() could do this looping for me!?? But how to code this.. :/
The logic of course is, if same OrganizationNr is in both tables _and_ TRANSACTIONS[TransactionDate] between some of the dates in any rows in Lists in AGREEMENTS table, then Yes should be written as filter to the last column of TRANSACTIONS table. Today this column only test for OrganizationNr as I've explained, and no TransactionDate testing unfortunately yet.
Gotta sleep some too - so long
Trond
Moderators
January 31, 2022
Offline
Can't really follow your intentions, based on text and a few screenshots. The xlsx you attached was the mock-up you provided earlier, but you changed the rules a bit by introducing an eleven year old transaction. How realistic is that? Despite the fact that supplier 330 has no contract that matches that particular date you want to see "yes" anyway. Obviously, the query returns "no".
And with regard to supplier 227, what would you want the outcome to be if we were to introduce a transaction dated 13 May 2019? Would such a transaction fall under both contracts, even though they might be different contracts all together? Or would you just accept a single "yes" because there is a contract?
And where does the column "VoucherNr" in the Merge query come from?
Obviously, my fairly simple solution is not capable of dealing with these complexity. Perhaps better that you upload a more realistic model of your data and include the queries that you have already created. That would make it so much easier:
Answers Post
June 22, 2019
Offline
Hi Riny,
Example dates can ba some old, in production at least those will be mostly 12 months old. Sorry for a bit random transactions dates if so. Agreements dates on the other hand can be like 10 years old... However, in my opinion this should not change the technical issues I’m facing.
Regarding your previous post in merge mode, the SupplNr 227 came twice, as both Yes and No at the amount of 450. See my post 27th May about this. Witch led be believe this case is not possible to solve by using just merge statements.
The .pbix file I uploaded last night is really all that I wanna solve. But need the looping script for Trancaction dates in between of aggrement dates.
Of course a transaction table in accounting consist of voucher numbers too, but this is still a minor difference in all. My transaction table in real possibly consist of 20+ columns (amount, TransactionDate and voucher number only some of those). But it’s no testing for voucher number.
DateFrom and DateTo are absolute dates, meaning if a transaction happens 13th May, and the agreements table goes from 13th May to end of May same year, then Yes should be written in column.
Thanks for at least trying to help.
Trond
Moderators
January 31, 2022
Offline
Fair enough. Then perhaps another rather simplistic approach will work, as demonstrated in the attached file. No need for "earliest_date".
June 22, 2019
Offline
Hi Riny,
Opened your last file. To last step Merged table: For SupplNr 227, is it random that No disappear, leaving Yes behind? (seems like a 50% chance to me, or is this functionality part of the Disctinct formula, always choosing Yes before No...?!)
Btw, liked your "earliest_date" formula. Will use it in future reports too.
Trond
Moderators
January 31, 2022
Offline
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.
June 22, 2019
Offline
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..)
Moderators
January 31, 2022
Offline
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:
June 22, 2019
Offline
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
Moderators
January 31, 2022
Offline
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.
1 Guest(s)
