仅从数据库获取我想要的数据，但保持结构 [英] Get only the data I want from a db but keep structure
问题描述
我正在尝试在powerbi上执行此操作，但我想它只是基本的SQL
我想在powerBi中导入数据，但使用一些id对其进行过滤。让我们来看这个例子。我有一些公司的分贝（表1）。每个公司都有一个建筑物（表2），每个建筑物都有雇员（表3）。
我想导入那三张表，但只导入一家公司（OnePowerBi大约是一家公司，永远不会更多）。请注意，每个表都具有父表的外键。我的模式在powerBI中运行良好
我试图在高级选项中进行SQL查询（在power BI上，您可以在选择哪个表导入之前执行一些SQL），但是如果这样做：
select *来自idcomp = 1
的公司
它将仅导入公司表。
如果这些数据链接到选定的公司ID，但我想导入所有表的所有数据，但要保留结构，而不是将所有数据作为内部连接保存在请求或表中会做。我应该怎么办？
此答案基于我对问题的理解。如果此解决方案不能满足您的需求，我建议您更新问题以阐明您的问题和所需的结果。
此外，我提前致歉对于这个答案的长度；我不知道您在Power BI方面的专业水平，我想确保涵盖我所采取的每个步骤。
注意-我我正在使用Windows应用商店提供的最新版本的Power BI Desktop。如果我执行的任何操作或发布的图片都不符合您的Power BI应用程序，请确保您使用的是最新版本。
0） 首先，我创建了一个包含三个表和一些数据的示例SQL结构。可以在
2）在此处输入您的SQL Server和数据库名称（我使用的是本地数据库，因此为。）。
3）仅选择公司表然后单击编辑...。
...其中
4）单击管理参数->新参数。
5）填写如下所示的表格。
6）返回公司查询中，单击 idcomp 列，然后转到数字过滤器->等于...
7）在弹出框中，单击第一个条件的下拉箭头并进行切换到参数。由于只有一个参数，它将自动填写。单击确定...
...来获取。
8）右键单击公司查询，然后单击重复。再次执行此操作，以便获得原始查询和两个副本。
9）返回原始查询，右键单击建筑物列，然后选择删除。
10）右键单击公司（2）查询，然后选择重命名。将第二个查询命名为Buildings。
11 。在建筑物查询中，右键单击建筑物列，然后选择删除其他列。
12）单击建筑物列上的展开箭头按钮，并取消选择除idbuild和BuildingName之外的所有内容（如下所示）。
13）右键单击公司 （3）查询并选择重命名。将第三个查询命名为雇员。
14 。在员工查询中，右键单击建筑物列，然后选择删除其他列。
15）单击建筑物列上的展开箭头按钮，并取消选择除雇员之外的所有内容（如下所示）。
12）单击展开箭头雇员列上的按钮，并取消选择除idemp，FirstName和LastName（如sho wn。
13）单击关闭，然后单击确定。
14）要检查数据，请制作一些表格，以查看仅加载了公司1的信息。
15）要更改加载的公司，请单击首页->编辑查询。
16）单击CompanyID（1）查询并将当前值更改为2。
17）点击关闭并关闭应用并注意表将更新为仅显示公司2的信息。
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?
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.
这篇关于仅从数据库获取我想要的数据，但保持结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
