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
herbemischung
Resolver I
Resolver I

merge queries additional columns

Hi,

 

i want to merge 2 Tables. 

 

The Tables have the following structures:

 

 Table A

Date       Account No     Account    Level1       Level2    Amount

1.1.17      4100               Bla 1          Revenue   Internal   100

5.2.17      4300               Bla 2          Revenue   External   150

10.3.17    5700               Bla 3          Personal   Workers   -300

17.4.17    6300               Bla 4          Fixed C.    Energy     -200

 

Table B

Date         Account No     Account     Level 1     Level 2    Amount      Budget

31.1.17                                                                Internal                      110

28.2.17                                                                External                      160

31.3.17                                                                Workers                     -350

30.4.17                                                                Energy                       -120

 

I want to add the Budget for each Level  2 and Month to Table A.

 

I also tried to make a separate Table and  create a Budget_ID with Month&Year&Level2 in both tables, but if there was no Amount in Level2 there would be no Budget too and if there are more than 1 etries in Level2 within a month, then i would get the Budget multiple times....

 

Hopefully you get my issue.

1 ACCEPTED SOLUTION
herbemischung
Resolver I
Resolver I

it turned out, that it was easier than I thought. 

 

I didn't know that by appending queries I can also add columns. So the solution to my probelm was just to append Table B to Table A.

View solution in original post

3 REPLIES 3
herbemischung
Resolver I
Resolver I

it turned out, that it was easier than I thought. 

 

I didn't know that by appending queries I can also add columns. So the solution to my probelm was just to append Table B to Table A.

Ashish_Mathur
Super User
Super User

Hi @herbemischung,

 

Write the following calculated column formula in TableA

 

=LOOKUPVALUE(TableB[Budget],TableB[Level 2],[Level2],TableB[Date],EOMONTH([Date,0))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-qiuyu-msft
Community Support
Community Support

Hi @herbemischung,

 

To  add the Budget for each Level  2 and Month to Table A, you can create a calculated column in Table A: 

 

Budget = LOOKUPVALUE('Table B'[Budget],'Table B'[Level 2],'Table A'[Level2])

 

For your second question, can you show the desired results based on the sample data you provided? 

 

Best Regards,
Qiuyun Yu 

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

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.