Power Query - 从表中选择列而不是之后删除 [英] Power Query - Select Columns from table instead of removing afterwards

问题描述

从数据库表(如 SQL Server)导入数据时的默认行为是引入所有列，然后选择要删除的列.

The default behaviour when importing data from a database table (such as SQL Server) is to bring in all columns and then select which columns you would like to remove.

有没有办法做相反的事情?即从表中选择您想要的列?最好不使用 Native SQL 解决方案.

Is there a way to do the reverse? ie Select which columns you want from a table? Preferably without using a Native SQL solution.

男:

let
    db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data],
    Sales_vDimCustomer = db{[Schema="Sales",Item="vDimCustomer"]}[Data],
    remove_columns = Table.RemoveColumns(Sales_vDimCustomer,{"Key", "Code","Column1","Column2","Column3","Column4","Column5","Column6","Column7","Column8","Column9","Column10"})
in
    remove_columns 

上面的片段显示了连接和随后的删除.

The snippet above shows the connection and subsequent removal.

对比原生SQL方式方式:

Compared to the native SQL way way:

= Sql.Database("sqlserver.database.url", "DatabaseName", [Query="
    SELECT Name,
           Representative,
           Status,
           DateLastModified, 
           UserLastModified,  
           ExtractionDate
    FROM Sales.vDimCustomer
"])

我在该步骤中看不到关于 }[Data], 值的太多文档，因此希望我可以劫持该字段以指定该数据中的哪些字段.

I can't see much documentation on the }[Data], value in the step so was hoping maybe that I could hijack that field to specify which fields from that data.

任何想法都会很棒！:)

Any ideas would be great! :)

我首先担心的是，当它被编译成 SQL 时，它会作为两个查询发送(正如在 ExpressProfiler 中看到的那样).

My first concern is that when this gets compiled down to SQL, it gets sent as two queries (as watched in ExpressProfiler).

第一个查询删除选定的列，第二个查询选择所有列.

The first query removes the selected columns and the second selects all columns.

我的第二个担心是，如果在数据库中添加或删除列，那么它可能会使我的报告崩溃(Excel 表格中的其他列会将您的结构化表格语言公式跳转到错误的列).这不是使用 Native SQL 的问题，因为它不会选择新列，并且如果列被删除，它实际上会崩溃，这是我想知道的.

My second concern is that if a column is added to or removed from the database then it could crash my report (additional columns in Excel Tables jump your structured table language formulas to the wrong column). This is not a problem using Native SQL as it just won't select the new column and would actually crash if the column was removed which is something I would want to know about.

推荐答案

在我重新思考并查看 docs.

Ouch that was actually easy after I had another think and a look at the docs.

<代码>让db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data],Sales_vDimCustomer = Table.SelectColumns((db{[Schema="Sales",Item="vDimCustomer"]}[Data],{名称"，代表"，地位"，"DateLastModified","UserLastModified",提取日期"})在销售_vDimCustomer

这也比其他方式加载速度快得多，并且只生成一个请求的 SQL 而不是两个.

This also loaded much faster than the other way and only generated one SQL requested instead of two.

这篇关于Power Query - 从表中选择列而不是之后删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！