cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Create calculated column on a table based on columns from two different other tables

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

 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!

Super User

@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

Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.