Self Referencing Table Problem

[Artefx]

Hi,

I having some issues extracting the required data from a table within SQL database.

The databse is linked to our MRP / ERP system so cannot be changed in any way.

The problem i am having is I have a BOM (Bill of Materials) table within the database from which i need to extract multiple levels of information.

The above pics show my problem. 

At the top level i only see the final part number (as this is whats on sales orders / forecasting), but i need the bottom level (the CW codes) to be able to estimate usage.

If anyone can point me in the correct direction it would be very much appreciated.

Regards

Lee

[Seth_C_Bauer]

@Artefx From your example this is what I can see that you want. It should just be a matter of joining the table in on itself to return the additional information you need, which would be aggregatable in various ways in Power BI...

CREATE TABLE #TEMP
(
ID1 nvarchar(20),
Desc1 nvarchar(20),
ID2 nvarchar(20),
Desc2 nvarchar(20)
)

INSERT INTO #TEMP (ID1, Desc1, ID2, Desc2) VALUES ('1','Desc1','5', '5Washer')
INSERT INTO #TEMP (ID1, Desc1, ID2, Desc2) VALUES ('1','Desc1','6', '6Washer')
INSERT INTO #TEMP (ID1, Desc1, ID2, Desc2) VALUES ('1','Desc1','7', '7Washer')
INSERT INTO #TEMP (ID1, Desc1, ID2, Desc2) VALUES ('5','5Washer','CW5', 'CWWasher')
INSERT INTO #TEMP (ID1, Desc1, ID2, Desc2) VALUES ('6','6Washer','CW6', 'CWWasher')
INSERT INTO #TEMP (ID1, Desc1, ID2, Desc2) VALUES ('7','7Washer','CW7', 'CWWasher')

    SELECT *
    FROM #TEMP t
	left outer join #TEMP x
		on x.id1 = t.id2
	where x.id1 is not null

[Artefx]

@Seth_C_Bauer Your example does indeed seemt to describe the issue i'm having. 

Could you please explain to someone who is relatively new to PowerBI where I actually run the code you demonstrated?

Please be aware that I can only read data out of the database. We do not have permission to create tables / view's as it is linked to some proprietary software. 

Regards

Lee

[Seth_C_Bauer]

@Artefx Sure. I created the temp table to show the example that you could run if you wanted to, since you didn't provide any details surrounding table name or column names.

But for your purposes, you don't have to create anything, just write a select query to return the information you want.

So, if we look at your table like this:

We can "Get Data" -> Sql Server -> set server/db

Then you have options to "Import" (pull data into PowerBI) - gives you all options to create a model/modify data etc.

or DirectQuery (Leaves the data in the source db, and lets you run queries against it. Limits what you can do to some extent in Power BI)

Under those options is "Advanced Options" - select this and you will see an area where you can input a query, instead of just selecting all the data from the table. I would recommend this anyway, because there is most likely a lot of unecessary info that you don't need.

Then, you can write a query that is similiar to what I showed you, but slightly modified to match the above picture. (replace Select * with only the columns you want returned.

    SELECT *
    FROM TEMP t
	left outer join #TEMP x
		on x.ID1 = t.ID2
	where x.ID1 is not null

As an example (Using my #Temp table), here is the query with the original table output, and the joined output. The first matches what you have, the second is what I believe you want and what the query gives you.

 

[Artefx]

@Seth_C_Bauer

Thanks a lot. That was very much appriciated. 

I have been using PowerBI / Power Query for 6 months or so so I'm totally aware of how to get information into PwBI and manipulate it. I have just never had to do any advanced query manipulation like this (in PwBI).

Again, thanks for your help,

Lee

[wonga]

@Artefx

Since it looks like you want to join a table with itself, you can probably duplicate the query in Query Editor and then use the "Merge Queries" feature to join the two and select the related columns and do your data modifications from there.

[Greg_Deckler]

This is a very difficult question to answer effectively. Can you provide more detail around "top" level and "bottom" level? Are these things in two different tables, are you working with views?

Without knowing anything about your data or systems, you could, in theory, create two different queries, one for the "top" level and one for the "bottom" level and do a join query based upon a common ID.