-2

I am trying to merge two tables in Powerquery. One is from a database, the other is from another source and has a set list of part numbers (pano20). Each part number has a source of supply (sos1) and some part numbers have two or more sources of supply each with differing unit costs (uncs). For example, part number 123 has a source of supply of both 000 and 530, but its unit cost under 000 is $1.50 and under 530 it is $2.50. There are 3 part numbers in my non-database table that I need to come from sos1 530 and the rest need to come from sos1 000. I am unsure which SQL to use, but I have used the one below. Obviously it is not working but I wanted to see if anyone had an idea of how I could do this.

use LIBCOM500

select sos1, uncs, ds18,
       case pano20
       when pano20 in ('ca2','oa1','oa1p') then sos1 = '530'
       else sos1 = '000'
from pcpprms0

Below is the desired output after merging the two tables.

Table 1 - excel table
|Part Number|Qty Needed|
|:---------:|---------:|
|    123    |     1    |
|    222    |     3    |

Table 2 - coming from database
|Part Number|SOS1|uncs|
|:---------:|:--:|---:|
|    123    |000 |1.50|
|    123    |530 |2.50|    
|    222    |000 |3.00|

Table 3 - Output from merging table 1 and 2 by part number
|Part Number|SOS1|uncs|Qty Needed|
|:---------:|:--:|:--:|---------:|
|    123    |530 |2.50|    1     |
|    222    |000 |3.00|    3     |
lberecek
  • 51
  • 5

2 Answers2

1

This would be the proper and common format of a case expression. You have too much in the expression and you didn't END it.

select sos1, uncs, ds18,
case 
 when pano20 in ('ca2','oa1','oa1p') then '530'
 else '000'
end sos1b
from pcpprms0
Isolated
  • 5,169
  • 1
  • 6
  • 18
  • The SOS1 is coming from a database. For example, some part numbers have two SOS1 values depending on which store it is coming from. The price differs from store to store. My problem is for parts equaling ca2, oa1, and oa1p, I need the prices from store 530, and for the rest of the parts I need the prices from store 000. – lberecek Mar 15 '23 at 17:44
  • That was not explained in the original question. Provide sample data and desired output. We have no idea if you are dealing with one or more tables now with this added detail. – Isolated Mar 15 '23 at 17:53
  • I apologize, this is my first question. There is one table from the database that I will be merging with a table by part number. I didn't add this to my question because I am doing the merge through power query. – lberecek Mar 15 '23 at 17:55
  • So do you have two price columns? One for store 530 and another from store 000? – Isolated Mar 15 '23 at 18:02
  • No, they are both included in the SOS1 column. So for example, say a part number has two prices at two different sources, it will show duplicates for that part number with two different prices in the dataset. – lberecek Mar 15 '23 at 18:07
  • And both would be in the SOS1 column – lberecek Mar 15 '23 at 18:08
  • for example, if I wanted all values to have a 000 sos1 my query would be select pano20, uncs, ds18, sos1 from pcpprms0 where sos1 = '000' but in this case I have 3 part numbers that I need to pull the price from 530 instead of 000. The rest need to come from 000. – lberecek Mar 15 '23 at 18:12
  • Provide sample data and expected output in your question. – Isolated Mar 15 '23 at 18:13
  • Added example tables to the question, does this explain it? @isolated – lberecek Mar 15 '23 at 18:29
0

Try this query may be it will help

select case when pano20 in ('ca2','oa1','oa1p') then '530' else '000'
end as sos1,uncs, ds18 from pcpprms0
sanjay bhansali
  • 327
  • 1
  • 10