Power Query 系列 (18) - 参数化查询

参数化查询增加了查询的灵活性。Power Query 可以设置和管理参数，同一工作簿下所有查询都可以使用。

但查询参数需要进入 Power Query 界面中设置和维护却不太方便，所以从更实用的角度来说，可以将参数设置在 Excel 工作表中，然后将此参数加载到Power Query 作为筛选条件。本篇以使用 Access 查询设计器轻松构造复杂 SQL 语句 (2)相同的数据进行类似的处理。我们发现，PQ 因为有查询编辑器提供的可视化操作，处理过程 SQL 语句更加简单。为了方便理解，这里也贴出 Access 中的表结构和表关系图。示例数据也已经上传到 github。

假设我们本次的处理要求是根据年份和月份为条件，得到所有物料的进出存报表。

获取数据连接

示例数据存放在 Access 数据库中，首先获取数据连接，将数据加载到 Power Query 中。操作步骤请参考Power Query 系列 (03) - 从数据库导入数据。因为不区分仓位，所以我们导入 3 个数据表的数据：

点击加载按钮，数据加载到 PQ。这个时候我们可能注意到一个现象，当有多于 1 个表数据加载的时候，默认就不会将数据上载到 Excel 工作表。如果我们需要将某个查询表的数据上载到工作表，选择右边部分的查询&连接，右键菜单中选择加载到菜单，选择数据上载的目的地。数据的目的地一共有 5 个，如下图所示。如果安装了 Power Pivot，可以将数据加载到数据模型。

设置参数

在工作表中设置如下图所示的筛选条件，使用 Ctrl + T，将筛选条件变为表格，并命名为 Criteria。

将表格 Criteria 加载至 Power Query，查询中以此作为数据筛选的条件。

进出存查询

以 stock_movement_details 为基础进行加工，将 stock_movement_details 复制一个新的查询命名为 stock_balance。删除 stock_balance 查询中与数据处理不相关的字段。

我们注意到，stock_movement_details 自动维护了与 stock_movement_headers 查询的关系，这个是 PQ 的智能之处。如果从类似 csv 文件导入，则不会有查询表之间的关系，我们可以通过合并查询的方法来维护查询表之间的关系。

对 stock_movemnt_headers 字段进行展开操作，保留 MovementType 和 DocDate 两个字段

完成该步骤后查询编辑器的界面如下：

与 movement_types 查询表进行合并查询操作，获取 movement_types 查询表的 InOutSign 字段：

对 movement_types 字段进行展开操作，保留 InOutSign 字段。完成后查询编辑器的界面如下：

根据 DocDate 字段添加两列 TxYear 和 TxMonth。在 PQ 中添加日期列也是非常方便。选中 DocDate 这一列，然后切换到【添加列】选项卡：

得到下面的结果：

用同样的方法添加 TxMonth 列：

得到 TxMonth 列：

根据 InOutSign 列添加一个自定义列：

接下来添加计算列，根据交易的年月和 InOutSign 来确定数据应该放到哪一列，是期初数量，还是期间出入库数量等等。进入高级编辑器，在 M 语言步骤的最前面添加两个变量：

    yearCriteria = Criteria{0}[year],
    monthCriteria = Criteria{0}[month],

同样的方法添加 PeriodOut 列。

添加期末余额的计算列：

删除不相关的字段，删除后查询编辑器界面如下：

使用分组依据计算合计数：

完成后查询编辑器界面如下：

此时对应的 M 语言脚本如下：

let
    yearCriteria = Criteria{0}[year],
    monthCriteria = Criteria{0}[month],
    
    Source = Access.Database(
        File.Contents("C:\Users\StoneWM\Desktop\stocks.accdb"), [CreateNavigationProperties=true]),

    stockMovementDetails = Source{[Schema="",Item="stock_movement_details"]}[Data],

    DeletedUnrelated1 = Table.RemoveColumns(
        stockMovementDetails,{"DocNo", "StorageLocation", "Remarks"}),
        
    ExpandedHeaderCols = Table.ExpandRecordColumn(
        DeletedUnrelated1, 
        "stock_movement_headers", 
        {"MovementType", "DocDate"}, 
        {"MovementType", "DocDate"}),

    MergedWithMovementTypes = Table.NestedJoin(
        ExpandedHeaderCols, 
        {"MovementType"}, 
        movement_types, 
        {"MovementTypeID"}, 
        "movement_types", 
        JoinKind.LeftOuter),

    ExpandedMovementTypeCols = Table.ExpandTableColumn(
        MergedWithMovementTypes, 
        "movement_types", 
        {"InOutSign"}, 
        {"InOutSign"}),

    InsertedYearCol = Table.AddColumn(
        ExpandedMovementTypeCols, 
        "TxYear", 
        each Date.Year([DocDate]), 
        Int64.Type),

    InsertedMonthCol = Table.AddColumn(
        InsertedYearCol, 
        "TxMonth", 
        each Date.Month([DocDate]), 
        Int64.Type),

    InsertedActualQtyCol = Table.AddColumn(
        InsertedMonthCol, 
        "ActualQty", 
        each if [InOutSign]="+" then [Qty] else -1 * [Qty]),

    InsertedBeginQtyCol = Table.AddColumn(
        InsertedActualQtyCol, 
        "BeginQty", 
        each if [TxYear] < yearCriteria or ([TxYear]=yearCriteria and [TxMonth]< monthCriteria) then [ActualQty] else 0),

    InsertedPeriodInCol = Table.AddColumn(
        InsertedBeginQtyCol, 
        "PeriodIn", 
        each if [TxYear] = yearCriteria and [TxMonth] = monthCriteria and [InOutSign] = "+" then [ActualQty] else 0),

    InsertedPeriodOutCol = Table.AddColumn(
        InsertedPeriodInCol, 
        "PeriodOut", 
        each if [TxYear] = yearCriteria and [TxMonth] = monthCriteria and [InOutSign] = "-" then [ActualQty] else 0),

    InsertedEndQtyCol = Table.AddColumn(
        InsertedPeriodOutCol, 
        "EndQty", 
        each [BeginQty]+[PeriodIn]+[PeriodOut]),
    DeletedUnrelated2 = Table.RemoveColumns(
        InsertedEndQtyCol,
        {"Qty", "MovementType", "DocDate", "InOutSign", "TxYear", "TxMonth", "ActualQty"}),
        
    Grouped = Table.Group(
        DeletedUnrelated2, 
        {"MaterialNo"}, 
        {{"期初余额", each List.Sum([BeginQty]), type number}, 
        {"入库数量", each List.Sum([PeriodIn]), type number}, 
        {"出库数量", each List.Sum([PeriodOut]), type number}, 
        {"期末余额", each List.Sum([EndQty]), type number}})
in
    Grouped

将数据上载工作表，在工作表中可以变更年度和月份作为筛选条件，然后在结果表中，右键刷新菜单，对计算结果进行刷新。

示例数据

github

• stocks.accdb
• Parameterized Query.xlsx