replace value in a table (based on value of other row with same foreign key)

[tvanover]

Hello everyone,

I have two tables linked with a common key

orderlines table:

Ordernumber	Orderline
1191	orderline_guid1
1191	orderline_guid2
1191	orderline_guid3
1191	orderline_guid4

deliverylines table:

Row	Orderline	Transportco	Weight
1	orderline_guid1	GLS	7
2	orderline_guid2	GLS	8
3	orderline_guid2		4
4	orderline_guid3		12
5	orderline_guid4		2

the link between the tables is 

[deliverylines].orderline many-to-one -> [orderlines].orderline

Now, I would like to modify the contents of the [deliverylines].transportco column in such a way that rows 3, 4 and 5 also contain the value GLS, based on the fact that both 5 rows are linked to the same Ordernumber (via the orderline key), and because at least one of these rows already has a value GLS for that column.

That's because I need to show the sum of the weights of all deliverylines linked to  the same ordernumber, and have the value GLS shown in the visual as well.

How can I achieve this in the power query editor? Either by directly manipulating the transportco column, or by creating another column with the desired values?

Thanks,

[v-robertq-msft]

Hi, @tvanover 

Glad to see you have solved your problem, would you like to mark your solution reply as a solution so that others can learn from your solution?

Thanks in advance!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tvanover · Accepted solution]

Following up on my own previous post, I solved my problem by:

- merging all related queries into 1 big table

- grouping in this table using a combination of sum and max, see screenshot:

[tvanover]

Hello,

Thanks for the excellen tutorial, I've followed these instructions on my pbix file, and it works.

But....

Ordernumbers are updated in realtime, and the report should also be updated automatically.

So if a new order is finished, I would want to see that same calculation happen for that ordernumber, without me having to add each time a new order to that conditionnal column.

So while your example works, it's static and I need something that does the samething automatically for each now ordernumber that pops up in my source table.

Do you see any solution to that as well?

appreciate all the effort!

tom

[tvanover]

Hello,

I've been thinking this over, and I'm thinking of another approach:

Please see this visual:

What I want to see is this:

1191 GLS 40.82 3/12/2020 16:07:21 (the datestamp always correspons to the order number)

Perhaps it's possible somehow to do a sum(gewicht) based only the field order, and display MAX(Transport) at the same time? 

I tried it with a measure, but it doesn't seem to take account of all the relations between the tables, because the output is now totally garbled.

You can download my test file here, if you want to have another go at it (or someone else) ?

https://www.dropbox.com/s/l4io6cetyl1nb3p/transport-problem.pbix?dl=0

[v-robertq-msft]

Hi, @tvanover 

According to your description, you want to make each column display “GLS” when [Ordernumber] is 1911, you can follow my steps:

1. Go to power query editor, select ‘deliverylines’ table, merge query like this:

2. Expand the table like this:

3. Add a conditional column, like this:

If you want to add other [Transportco] for other Ordernumbers(like 1192, 1193), you can click add clause to add other coditions.

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.