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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ancorte
Frequent Visitor

how to connect 2 table depending on the values

Hello,

i have 2 identical table (with 2 columns , "account id" - "amount"), and i want to create a third table that make this:

- contains all the "account id" from the first table

- the column amount is equal the amount in the table 2 if there are some values (not zero) otherwise take the value of the table 1

How can i do ?  UNION make all the 2 tables together, and i can't use DISTINCT(UNION....).

Thanks for the help.

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @ancorte ,

 

Please try:

First create a new table:

 

New Table = UNION( EXCEPT( SUMMARIZE('Table 2','Table 2'[Account ID]),SUMMARIZE('Table 1','Table 1'[Account ID])),SUMMARIZE('Table 1','Table 1'[Account ID]))

 

Then add column:

 

Ammount = IF(ISBLANK( MAXX(FILTER('Table 2',[Account ID]=EARLIER('New Table'[Account ID])),[Amount])), MAXX(FILTER('Table 1',[Account ID]=EARLIER('New Table'[Account ID])),[Amount]),MAXX(FILTER('Table 2',[Account ID]=EARLIER('New Table'[Account ID])),[Amount]))

 

Output:

vjianbolimsft_0-1658397697251.png

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
ancorte
Frequent Visitor

Hello,

sorry for my this reply after a lot of time.

Your suggestion is OK, but i have a problem, because in table 1 for example i have all account id with tha date, so for example i have the account id 72/05/010 with an amount at 30/04/2022 and another amount at 30/06/2022

When i create the new column, in fact i need to have a column with an amount with tha date of the table 1 and/or table 2.

So the New Table, has Account id, Amount and date.

What can i do ?

Thanks for very precious help (is the first time that i see the function EARLIER...)

v-jianboli-msft
Community Support
Community Support

Hi @ancorte ,

 

Please try:

First create a new table:

 

New Table = UNION( EXCEPT( SUMMARIZE('Table 2','Table 2'[Account ID]),SUMMARIZE('Table 1','Table 1'[Account ID])),SUMMARIZE('Table 1','Table 1'[Account ID]))

 

Then add column:

 

Ammount = IF(ISBLANK( MAXX(FILTER('Table 2',[Account ID]=EARLIER('New Table'[Account ID])),[Amount])), MAXX(FILTER('Table 1',[Account ID]=EARLIER('New Table'[Account ID])),[Amount]),MAXX(FILTER('Table 2',[Account ID]=EARLIER('New Table'[Account ID])),[Amount]))

 

Output:

vjianbolimsft_0-1658397697251.png

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vanessafvg
Super User
Super User

can you share some sample data?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Table1    
Account IDAccount NameAmountDataId Origine Dato Contabile
66/15/025COMPONENTI C/ACQUISTI-10.225,0030/04/20221
66/25/504ACQUISTO MATERIALE VARIO-658.522,0030/04/20221
     
     
     
     
table 2    
Account IDAccount NameAmountDataId Origine Dato Contabile
64/10/020CONT.P/ONERI SOC.E P/COSTO PERS.                                 33330/04/20224
66/15/025COMPONENTI C/ACQUISTI                                 16730/04/20224
66/25/504ACQUISTO MATERIALE VARIO                                    -  30/04/20224
66/25/507ACQUISTO BENI E MAT PER ATTREZZA                                 66730/04/20224
66/30/007MATER.MANUT.BENI PROPRI(LIM.5%)                                    -  30/04/20224
66/30/015MATERIALE DI PULIZIA                                    -  30/04/20224

 

If in table 1 for the same account ID (with the same date) there is a value different from " ", i want that in new table Amount 2 has the value of Table 2 ; instead for the same account ID (with the same date) has a value equal " " i want that in new table Amount 2 has the value of the corresponding row in Table 1.

 

In the example for account id 66/15/025 in Table1 there is a value in Table 2 : so the new table have to take the value in Table 2 (= 167)

In the example for account id 66/25/504 in Table1 there is a value in Table 2 equal to " ": so the new table have to mantain  the value in Table 1 (= -658.522)

I hope that it's clear. Thanls for your help.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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