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
JotaEfe
New Member

Create table using 2 tables and a third with multipliers

Hello!

 

I hope I can explain what im trying to attemp

 

I have 3 tables:

 

Table 1& Table 2 have the following structure

 

DATE | Name | Value

(date)| ID 1  | Value 1

 

I have many ID's with a vuale for each date

Both tables have the same ammount of dates but different IDs and values

 

I want to mix both tables using a third table wich is like this:

 

ID Table 1 | ID Table 2 | factor

a               |  b              |0.2

a               |  c               |.4

 

And so on 

 

I want to create a new table that prints the following column:

Date    |   ID  Table 1 |   Value

 

Where value = value(ID1 Table 2) * factor(Corresponding to that id) + value(ID2 Table 2) * factor .....

 

I hope I was clear enough if not i can bring an example

 

Thanks in advance

 

1 ACCEPTED SOLUTION

@JotaEfe 

you can do this in PQ

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
ryan_mayu
Super User
Super User

@JotaEfe 

pls provide the sample data and expected output.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




These are the 3 tables

 

DATEIDProduct
1/1/2021A123
2/1/2021A121
3/1/2021A128
1/1/2021B243
2/1/2021B233
3/1/2021B242
1/1/2021C565
2/1/2021C566
3/1/2021C556

 

DATEID2Product2
1/1/2021J5
2/1/2021J6
3/1/2021J5
1/1/2021Q3
2/1/2021Q2
3/1/2021Q3
1/1/2021M1
2/1/2021M1
3/1/2021M1.1

 

 

ID1ID2f
AJ0.5
AQ0.3
BJ0.5
BQ0.3
BM0.5
CM0.5

 

 

This is the desired output: 

 

DATEIDProductAsoc. Prod
1/1/2021A1233.4
2/1/2021A1213.6
3/1/2021A1283.4
1/1/2021B2433.9
2/1/2021B2334.1
3/1/2021B2423.95
1/1/2021C5650.5
2/1/2021C5660.5
3/1/2021C5560.55

 

For example in the first row Asoc prod = 5 * 0.3 + 3 *0.3

@JotaEfe 

still not clear about the logic

the first row A 123,  2021/1/1.

in the third table ,we can find J and Q which is connected to A. which is 0.5 and 0.3

should the formula be 5*0.5+3*0.3?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes, it was a typo sorry

@JotaEfe 

you can do this in PQ

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks! didnt know you could do it with PQ

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.