Conditional column result returns a list – should return a value

[apoje]

Hi, I have the below formula, where if the condition is met the result is pulled from the other query which is also loaded in the same excel workbook. The result should be a single value, but it returns a list.

= Table.AddColumn(#"Changed Type1", "EndPurchasePrice", each if [OrderDate.date] <= #date(2020, 9, 24) then Variation_PurchasePrice[PurchasePrice] else [OrderItemAmount.purchasePrice])

How can I get around it?

I know I could do a merge and do a conditional column on single query but I would just do a lot of repetitive steps which I wish to avoid.

Thanks for the help!

Andraz

[PhilipTreacy]

Hi @apoje 

You'll have to join the tables from the two queries then create your conditional column from the joined table.

Phil

[Jimmy801]

Hello @apoje 

you are referencing a column, and is shown as list. In Power Query there are no relationships, so it doesn't pick up the related row. However, if you would like to reference the first item of this list, use this reference instead

Variation_PurchasePrice[PurchasePrice]{0}

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[apoje]

My desired result is a returned purchase price of an item that relates to the unique variation number.

I would like to set the condition if fulfilled to pull the relevant data from the connected query, and if it is not met it'll take the data from the active query/table.

As @Jimmy801 pointed out since there is no relationships in PQ (which I did not know) I get out the whole list. And only specifying the first row would not solve anything, because 1 row is a unique purchase price per unique item.

Is there any way to solve this in a way I started this out?

I have it solved with merged queries – conditional column – and than deleted the columns I do not need à I just think this is a very inefficient way to go about it…

@PhilipTreacy  I cannot share the dataset: but it is sales data from the ERP. I want to update past purchase prices since they were not correctly written in the system.

Thanks!

Andraz

[Jimmy801]

Hello @apoje 

in Power Query you have to join both tables and then expand the column of your joined table. This is how Power Query works

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[PhilipTreacy]

Hi @apoje 

That step will not result in a single value. Your iterating over each row in the column [OrderDate.date] so you end up with a column.

What exctly are you trying to do?  Why do expect the result to be a single value?

Please supply the data too otherwise I'm just guessing at what you are working with.

Phil