1

I have 2 data sets which i would like to create relationships for (there is a third i would like to join but the first 2 are priority). They all have a common link that is design_ID.

I did have a picture showing what i have tried. but i do not have 10 rep points yet... so i will try to describe it. I have 2 datasets with a join tables that i have read about. i have both datas sets connected to the join table which is all the areas in a table.

The design_ID is also broken down to area, strip and cut. All of these columns have multiply entries (10s to 100s each).

Ideally what I would like to be able to pull is info from any data set using the design_id, area, strip or cut as a slicer/search parameter.

edit: I have been watching a few youtubes on powerquery thinking this may be the way to go?

any help is appreciated

Dan

Dan171
  • 55
  • 7
  • post your image to a image share site, and put the link in your question. Someone with enough rep will edit to add the image. – guitarthrower Mar 02 '15 at 18:39

2 Answers2

1

You need a unique list of design_ID's. If you can't select a third data set with a unique list then you could copy the design_ID's from both data sets into a new worksheet Remove Duplicates and then Create Linked Table which you can use to join to data set 1 and data set 2.

As you are using a SQL server then you can create the third data set using a sql query. When you say "2 sql database" if you mean 2 databases on the same sql server then you can create a unique list by creating a connection to database1 and writing a query which will pull a unique list from both databases. 

SELECT DISTINCT [design_ID]
FROM [table1]
UNION
SELECT DISTINCT [design_ID]
FROM [database2].[dbo].[table2]

If you mean 2 databases on 2 different SQL servers the you could ask your DBA if it is possible to create a linked server from Server1 to Server2. If this can be done then you can use the following. Note you put the Linked Server name in front of database2.

SELECT DISTINCT [design_ID]
FROM [table1]
UNION
SELECT DISTINCT [design_ID]
FROM [server2].[database2].[dbo].[table2]
JB 1881
  • 81
  • 5
  • i have tried making unique lists of the area and strips. there are too many design_ID to make unique list of and always new ones being created daily. – Dan171 Mar 02 '15 at 07:06
0

You don't mention how you are getting your data. But for me, my data tends to come from databases. So when I have a similar situation, I import a 3rd table using a SQL query that pulls just the unique items I am looking for.

guitarthrower
  • 5,624
  • 3
  • 29
  • 37
  • It's from 2 sql database. The first is a design database which has multiply entries about the break down of each component (a weight and grade). while the 2nd is a finalized & loaded Db (also weight and grade). Both have multiply parts for each component. What I would like to do is load the sum for each component. I hope that makes sense! – Dan171 Mar 03 '15 at 23:52