Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to create a column in one table that is based in two other tables. The desired column (placed in the UNION table) should be able to say the amount of customer order schedule lines and purchee order schedule lines (CUSTO and PURCO).
Customer order schedule line table (CUSTO):
Doc_nr | Item_nr | Schedule_line_nr | Order_qty | Confirmed_qty | Delivery_date | |||||
0002658769 | 0010 | 001 | 10 | 10 | 01.04.24 | |||||
0002658769 | 0020 | 001 | 50 | 45 | 01.04.24 | |||||
0002658769 | 0020 | 002 | 0 | 5 | 05.04.24 | |||||
0002185398 |
| 0010 | 001 | 100 | 60 |
| 10.04.24 | |||
0002185398 | 0010 | 002 | 0 | 20 | 14.04.24 | |||||
0002185398 | 0010 | 003 | 0 | 20 | 20.04.24 |
Purchase order schedule line (PURCO):
Doc_nr | Item_nr | Schedule_line_nr | Order_qty | Confirmed_qty | Delivery_date | |||||
0004598731 | 0010 | 001 | 30 | 0 | 04.04.24 | |||||
0004598731 | 0010 | 002 | 0 | 30 | 11.04.24 | |||||
0004592147 | 0010 | 001 | 45 | 45 | 05.04.24 |
Header of the documents (UNION):
Doc_nr | Item_nr | Order_qty | Conf_qty | Source_table | Desired column (Schedule line amount) | |||||
0002658769 | 0010 | 10 | 10 | CUSTO | 1 | |||||
0002658769 | 0020 | 50 | 50 | CUSTO | 2 | |||||
0002185398 |
| 0010 | 100 | 100 | CUSTO | 3 | ||||
0004598731 |
| 0010 | 30 | 30 | PURCO | 2 | ||||
0004592147 |
| 0010 | 45 | 45 | PURCO | 1 |
Relationships:
- PURCO n:1 UNION
- CUSTO n:1 UNION
I tried formulas for a calculated column with SUMMARIZE or LOOKUPVALUE but it is not working.
One example of what I tried:
TEST =
IF(
UNION[Source_table) = "PURCO",
SUMMARIZE(PURCO, PURCO[Doc_nr], PURCO[Item_nr], "amount sched. line", COUNT(PURCO[Schedule_line_nr]),
SUMMARIZE(CUSTO, CUSTO[Doc_nr], CUSTO[Item_nr], "amount sched. line", COUNT(CUSTO[Schedule_line_nr])
)
Can you help me? Thank you very much!
@jessicarocha , You can use merge in Power Query or you can use Lookup, related, related table, Filter with joins. But in all cases tables from one table at a time and you can further do calculations
refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
100 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
106 | |
104 | |
85 | |
73 |