Filetring my table before importing in PowerBi

[osso]

Hi, 

Sorry for my english, it's not my main language.

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). Ecah companies have 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 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 a inner join will do. How should i do ? 

Thanks for reading, 

Regards

[Eric_Zhang]

---

@osso wrote:

Hi, 

Sorry for my english, it's not my main language.

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). Ecah companies have 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 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 a inner join will do. How should i do ? 

Thanks for reading, 

Regards

---

@osso

You  can create a text type parameter companyID and create 3 queries individually with that parameter. Once the parameter changes, the data in those 3 queries change accordingly.

Company

let
    Source = Sql.Database("servername", "master", [Query="select * from company where idcomp="&companyID])
in
    Source

Building

let
    Source = Sql.Database("servername", "master", [Query="select * from building where idcomp="&companyID])
in
    Source

Employees

let
    Source = Sql.Database("servername", "master", [Query="SELECT * FROM Employ E#(lf)WHERE EXISTS#(lf)(#(lf)SELECT 1 FROM building B WHERE idcomp="&companyID&" AND E.buildingid=B.buildingid#(lf))"])
in
    Source

By the way, just in case you're not aware, maybe you could import all data and apply Row Level Security to report viewers.

[osso]

Hum i thought about that ... But as i have 30 tables it will be a pain. I was wondering if there is an other option as an "on cascade" but for a select 😕
Anyway, thank you very much for your help

[osso]

Hi tried some stuff like that but PowerBi insert only the first request ...

DECLARE @monID INT;
SET @monID =xxxxx;
select * from APN_Insertion_Order where ID_Insertion_Order=@monID;
select * from APN_Campaign where ID_Insertion_Order=@monID;

SELECT *
FROM APN_Creatives
WHERE EXISTS
(SELECT *
FROM APN_Campaign
WHERE APN_Creatives.ID_Campaign = APN_Campaign.ID_Campaign and APN_Campaign.ID_Insertion_Order=@monID)

[osso · Accepted solution]

This man did your job : https://stackoverflow.com/questions/46715097/get-only-the-data-i-want-from-a-db-but-keep-structure

Thanks.