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?

can you provide sample data in your database? sounds like you want to JOIN the 3 tables — DHLopez
As hard as I could Understand, you want import data from all three tables, that corresponds to a particular company (idcomp =1). If So, Do some inner join and restrict data from the company you need (in where clause) Is this what you are looking for? — Valli
@SeanLange - PowerBI is a weird beast...works as it's own ETL tool as much as a reporting suite, but to some degree it's just a fancy spreadsheet with some data import tools attached. So you get people with no Database background putting together scripting and I suspect he is missing what the * in the select statement is and how it can be defined. Lowteast - you can specify field names or table.* if you don't want all columns returned from an inner join : select t2.* from t1 inner join t2 on t1.key = t2.key where t1.idcomp = 1 — Twelfth
Hey, my main problem was to keep the table structure in power BI. Which mean, not have all the stuff i want in one request. @Joe answer me perfectly. This problem can seems stupid but in power bi it's different, for real. Anyway, thansk for reading and for your help :) — Lowteast

Joe Gravelyn Joe Gravelyn · Accepted Answer · 2017-10-12T22:49:00

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.

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

1 Answers