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
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]