Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
OB
Helper II
Helper II

How to iterate through two tables to create a new column?

Hello,

 

I'm looking for some input or a full a solution for the problem below.  Also, I assume getting a solution using Power Query will be better, so that all transformations are done before the column loads into the report, but if there's a nice solution in DAX, please advise as well.

 

See the sample in the .xlsx Source File.  There are three tabs for three tables. 

 

Customers table has unique customer IDs

Transactions table lists all transactions for all customers. There can be many transactions for the each customer. A column has to be created in this table, which I called "TransTerms to be created".  It will be based on the rule applied to the third table: Contracts.

Contracts table contains all contracts - there can be many contracts for the same customer, depending on the time period.

 

What should be done is:

1) Take the Trans Date for Transaction1 from the Transaction table.

 

2) Go over all Contracts for that Customer in the Contracts table (there are three of them in the sample). 

 

3) Take the value from the Terms column for the record (contract) where the Transaction Date falls between Start and End dates in the Contracts table and write it to the new column "TransTerms to be created" in the Transactions table.

 

4) If the Transaction Date from the Transaction Table does not fall between any Start and End dates listed for that customer in the Constracts table, then write the value "30" in the "TransTerms to be created" column in the Transactions table against Transaction1.

 

5)  If the Customer has no contract listed in the Contracts table, write "30" in the "TransTerms to be created" column in the Transactions table against that transaction.

 

6) Repeat the same steps for Transaction2, i.e. take the Transaction Date for the Transaction2 from the Transaction table for the same customer and compare to all Contracts for that customer in the Contracts table.  Take the Terms value for the Contract where the Transaction Date is between Start and End dates and write it to the TransTerms to be created" column against Transaction2 in the Transactions table.  Otherwise, write "30".

 

7) Keep iterating like that through both tables to fill in "TransTerms to be created" column in the Transactions table.

 

Thanks a lot for your time!

2 REPLIES 2
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @OB,

 

I failded to download the attached source file, would you please share it via other tool, like OneDrive?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Yuliana,

 

I verified that the link works on my corp and on the external network for other people, so maybe you could try again? I shared it from my google drive via a shareable link.  I don't have OneDrive account with the option to share files.  The sample file is very small, so I've typed in data manually below and hopefully you can copy it in a new Excel file.

 

Customers:

123456

654321

987654

100000

 

Transactions:

Customer   TransNumber                 TransDate                   TransTerms Column To Be Created

123456             1                                   01/05/2005                               30

123456             2                                   10/03/2012                               15

123456             3                                   15/01/2018                               7

654321             101                               31/07/2009                               20

654321             201                               01/08/2010                               10 

654321             301                               31/05/2013                               20

654321             401                               01/12/2017                               25

987654             102                               01/02/2018                               30

100000             103                               01/03/2018                               30

 

 

Contracts:

 

Customer          Period               Start                         End                 Terms

123456                1                     01/01/2010            31/12/2011          20

123456                2                     01/01/2012            15/03/2012          15

123456                3                     16/03/2012            31/12/2099          7

654321                1                     01/07/2009            30/04/2010          20

654321                2                     01/05/2010            31/08/2010          10

654321                3                     01/09/2010            30/06/2013          20

654321                4                     01/07/2013            31/12/2099          25

987654                1                     01/03/2007            25/10/2007          20

 

Thanks for looking into this!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.