Combining 2 databases

[Anonymous]

Hi,

I have 2 datasets - Source SQL server and csv file.

Dataset 1 - has data about an item and size

Dataset 2 (CSV) - has pricing and vendor info

I am trying to combine two datasets to calculate and compare the pricing for various sizes from each vendor. i cannot map them directly because the pricing for the vendor is range based. CSV has the Vendor name, low and high range with pricing

Ex:  item A with size - 10 could have different prices from each vendor.

If i were to script this in SQL, it would be as below

Select *, (size * price)  as Costpersize

From dataset1

Left join dataset2

On size between lowrange and highrange

i am newbie and looking to replicate this in PowerBI ? TIA

[v-frfei-msft]

Hi @Anonymous,

Based on your description, I entered the sample data and have a test. We can take the following steps to get the Costpersize column here.

1. Merge two tables based on item column and expand necessary columns.

2. Create a new column in the merge table, and filter the rows.

Column = IF(Merge1[Dataset 2.highrange]>Merge1[size] && Merge1[Dataset 2.lowrange]<Merge1[size],1,0)2To get the Costpersize by adding a new calculate column.

3. To get the Costpersize by adding a new calculate column.

Costpersize = Merge1[size]*Merge1[Dataset 2.price]

Then we can get the result as below.

For more details, please check the pbix as attached. If the above method doesn’t return your expected result, please share sample data of your tables.

https://www.dropbox.com/s/074b7ty9z6s09mp/combine.pbix?dl=0

Regards,

Frank

[Anonymous]

Thanks Frank.. I took the SQL approach and moved on as an alternative but i will try your solution and make the changes in PowerBI, if it works. i'll let you know