Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
66 | |
62 | |
62 |
User | Count |
---|---|
199 | |
120 | |
110 | |
79 | |
69 |