Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
you can do this in PQ
pls see the attachment below
Proud to be a Super User!
pls provide the sample data and expected output.
Proud to be a Super User!
These are the 3 tables
DATE | ID | Product |
1/1/2021 | A | 123 |
2/1/2021 | A | 121 |
3/1/2021 | A | 128 |
1/1/2021 | B | 243 |
2/1/2021 | B | 233 |
3/1/2021 | B | 242 |
1/1/2021 | C | 565 |
2/1/2021 | C | 566 |
3/1/2021 | C | 556 |
DATE | ID2 | Product2 |
1/1/2021 | J | 5 |
2/1/2021 | J | 6 |
3/1/2021 | J | 5 |
1/1/2021 | Q | 3 |
2/1/2021 | Q | 2 |
3/1/2021 | Q | 3 |
1/1/2021 | M | 1 |
2/1/2021 | M | 1 |
3/1/2021 | M | 1.1 |
ID1 | ID2 | f |
A | J | 0.5 |
A | Q | 0.3 |
B | J | 0.5 |
B | Q | 0.3 |
B | M | 0.5 |
C | M | 0.5 |
This is the desired output:
DATE | ID | Product | Asoc. Prod |
1/1/2021 | A | 123 | 3.4 |
2/1/2021 | A | 121 | 3.6 |
3/1/2021 | A | 128 | 3.4 |
1/1/2021 | B | 243 | 3.9 |
2/1/2021 | B | 233 | 4.1 |
3/1/2021 | B | 242 | 3.95 |
1/1/2021 | C | 565 | 0.5 |
2/1/2021 | C | 566 | 0.5 |
3/1/2021 | C | 556 | 0.55 |
For example in the first row Asoc prod = 5 * 0.3 + 3 *0.3
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?
Proud to be a Super User!
Yes, it was a typo sorry
you can do this in PQ
pls see the attachment below
Proud to be a Super User!
Thanks! didnt know you could do it with PQ
you are welcome
Proud to be a Super User!
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |