Get only the data I want from a db but keep structure

Question

I'm trying to do this on powerbi but I guess it's just basic SQL

I would like to import my data in powerBi but filtering it with some id. Let's take this example. I have db with some companies (table 1). Each compay has building (table2) and each building has employees (table3). I would like to import thoses three table but only for a company ( OnePowerBi is about One company, never more). Please notice that each table has the foreign key of the parent one. My schema works well in powerBI

I tried to make SQL query in advanced option ( on power BI you can do some SQL before chossing which table import) but if I do:

select * from companies where idcomp=1

It will only import the company table. I would like to import all the data of all the tables if thoses data are linked to the selected company ID but keep the structure and not get all the data in a "request" or "table", as an inner join will do. How should I do?

Answer 1

This answer is based off my understanding of the question. If this solution doesn't get you what you want, I would suggest updating the question to clarify what your problem and desired result is.

Also, I apologize ahead of time for the length of this answer; I don't know your level of expertise with Power BI and I wanted to make sure to cover every step I took.

Note - I am using the latest version of Power BI Desktop available through the Windows Store. If any of the actions I take or pictures I post don't align with your Power BI application, make sure you are using the latest version.

0) As a start, I created a sample SQL structure with three tables and some data. The SQL code I ran can be found here.

1) In Power BI, click on Get Data -> SQL Server

2) Enter your SQL server and database name here (I'm using a local DB, hence the ".").

3) Select only the Companies table and click Edit...

... which imports like this.

4) Click on Manage Parameters -> New Parameter.

5) Fill out the form as shown below.

6) Back in the Companies query, click on the dropdown arrow for the idcomp column and go to Number Filters -> Equals...

7) In the pop up box, click the dropdown arrow for the first condition and switch it to Parameter. Since there is only one parameter, it should automatically fill in. Click OK...

... to get this.

8) Right click on the Companies query and click Duplicate. Do this a second time so you have the original query and two copies.

9) Back in the original query, right click on the Buildings column and select Remove.

10) Right click on the Companies (2) query and select Rename. Name this second query Buildings.

11) In the Buildings query, right click on the Buildings column and select Remove Other Columns.

12) Click on the expansion arrows button on the Buildings column and deselect everything except idbuild and BuildingName (as shown below).

13) Right click on the Companies (3) query and select Rename. Name this third query Employees.

14) In the Employees query, right click on the Buildings column and select Remove Other Columns.

15) Click on the expansion arrows button on the Buildings column and deselect everything except Employees (as shown below).

12) Click on the expansion arrows button on the Employees column and deselect everything except idemp, FirstName, and LastName (as shown below).

13) Click Close & Apply.

14) To check the data, make a few tables and see that only company 1's information has been loaded.

15) To change which company is loaded, click on Home -> Edit Queries.

16) Click on the CompanyID (1) query and change the Current Value to 2.

17) Click Close & Apply and notice that the tables update to only show company 2's information.