Error with merged table data, but can see the data in the row.

[trev1972a]

Hi All, apologies for posting twice in one day- ive been puzzled by this for hours however

I have two tables salesorderitems and worksorders. And have merged those two tables since i wanted to do a conditional column based on values from both tables

The column is pretty simple - when salesorderitems.sostatus = 'ACTIVE' and worksorders.wostatus = 'ACTIVE, return a value of ACTIVE, otherwise return a value of COMPLETE (see below)

When i refresh the queries about 80% of the rows show the correct value but some say 'error' with the error being that salesorderitems.sostatus isnt found for that record (below)

However when i look at the actual record, - i can see that salesorderitems.sostatus has a valid value for the row!

(below)

How is this possible? Is it possibly related to me renaming columns? or perhaps a date filter? i am totally confused about the error when i can see the record data.

Thanks.

[BA_Pete]

Hi @trev1972a ,

Have you tried expanding the columns that you need from the merged table first before you add the calculated column?

There appears to be an error on the row that you are referring to in the salesorderitems table. I don't know, but this may be causing a read issue when the table hasn't been expanded.

[trev1972a]

Hi Pete

Ive expanded the columns i need, and as i wasnt sure if i had done it first i refreshed each query seperately

Ive just had a closer look at the data - and it appears the error actually occurs whenever worksorders.wostatus = 'ACTIVE'

Which suggests there is something wrong with my logic on the conditional column but i cant for the life of me see what

Ill try removing and re-adding the conditional column and sorry for so many posts all

[BA_Pete]

I don't think the column logic is wrong, the error is throwing when Power Query is assessing the logic.

Might be interesting to swap your tables around in your IF statement e.g. IF(A and B) then... goes to IF(B and A). This might make the condition get assessed in a different order and may give you different results. Unlikely to fix it, but getting different results should help to triangulate the issue.

[trev1972a]

Hi Pete

Actually switching the logic around worked- but i have no idea why. Of course now that conditional column isnt working for charting! despite looking good in tabular data

But rather than post daily and drive everyone mad- ill play with it for a week or so, i think this is going to take longer to pick up than i expected.

Thanks again