Power Query 循环 [英] Power Query Looping

问题描述

我使用Power Query提取了过去一个月中测试的所有唯一商品类型:

I used Power Query to pull all of the unique Item Types tested in the past month:

let
  Source = Sql.Database("XXX", "YYY"),
  dbo_tblTest = Source{[Schema="dbo",Item="tblTest"]}[Data],
  #"Filtered Rows" = Table.SelectRows(dbo_tblTest, each Date.IsInPreviousNMonths([Test_Stop], 1)),
  #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Start([Item],5)),
  #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Custom"})
in
  #"Removed Duplicates"

获得:

1. Test_ID ---项目--- Test_Start --- Test_Stop ---自定义
2. 2585048 --- B1846-6-02 --- 1/14/2014 12:46 --- 6/25/2015 14:28 --- B1846
3. 2589879 --- B1843-5-05 --- 12/23/2013 16:46 --- 6/25/2015 14:19 --- B1843
4. 2633483 --- B1907-1-04 --- 2014年8月21日20:47 --- 2015年6月10日6:20 --- B1907
5. 2638786 --- B1361-2-04 --- 6/13/2013 14:21 --- 6/16/2015 14:15 --- B1361
6. 2675663 --- B1345-2-02 --- 5/23/2014 18:39 --- 6/25/2015 21:27 --- B1345

接下来，我想使用Power Query对Query1中列出的每种项目类型进行过去10次测试，而与时间段无关.我想出了如何分别提取过去10个针对项目类型的测试，但不能在一个查询中将所有测试汇总在一起的方法.

Next, I want to use Power Query to pull the past 10 tests for each of the Item Types listed in Query1, regardless of time period. I figured out how to pull the past 10 tests for the Item Types separately, but not all together in one query.

let
    Source = Sql.Database("XXX", "YYY"),
    dbo_tblTest = Source{[Schema="dbo",Item="tblTest"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_tblTest, each Text.StartsWith([Item], "B1846")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Test_Stop", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",10)
in
    #"Kept First Rows"

获得:

1. Test_ID ---项目--- Test_Start --- Test_Stop ---值
2. 11717643 --- B1846-6-02 --- 7/23/2015 12:48 --- 7/23/2015 12:57 --- 43725341
3. 11716432 --- B1846-1-21 --- 7/23/2015 10:23 --- 7/23/2015 10:29 --- 43724705
4. 11715802 --- B1846-1-21 --- 7/23/2015 9:28 --- 7/23/2015 10:29 --- 43724720
5. 11715505 --- B1846-1-21 --- 7/23/2015 8:59 --- 7/23/2015 9:06 --- 43724675
6. 11715424 --- B1846-1-21 --- 7/23/2015 8:36 --- 7/23/2015 8:59 --- 43724690
7. 11713680 --- B1846-1-55 --- 2015/7/23 5:50 --- 7/23/2015 6:07 --- 43725239
8. 11691169 --- B1846-6-04 --- 7/20/2015 22:47 --- 7/22/2015 20:18 --- 43642835
9. 11690466 --- B1846-6-04 --- 7/20/2015 21:30 --- 7/22/2015 18:41 --- 43642729
10. 11701183 --- B1846-1-140 --- 7/21/2015 21:34 --- 7/21/2015 22:24 --- 43667358
11. 11701184 --- B1846-6-04 --- 7/21/2013 20:35 --- 7/21/2015 20:46 --- 43667359

是否可以使用Power Query在一个查询中提取所有需要的数据?如果没有，是否可以将VBA与Power Query一起使用来完成它?

Is it possible to use Power Query to pull all needed data in one query? If not, is it possible to use VBA with Power Query to get it done?

推荐答案

在Power Query中，如果您正在考虑如何循环，则通常会找到满足您需要的高阶库函数.在这种情况下，它是分组.

In Power Query if you're thinking about how to loop, you often find a higher-order library function that does just what you want. In this case, it's grouping.

分组通过某个键(在您的情况下，第一个表的 Custom 列)将表拆分.您可以使用 Table.TransformColumns 将保留10个以上"逻辑重写为在每个分组表中应用的函数，然后将分组表扩展回一个平面表.

Grouping splits up a table by some key, in your case the Custom column of the first table. You can rewrite your "keep past 10" logic into a function that you apply within each grouped table using Table.TransformColumns, then expand the grouped tables back out into one flat table.

您的查询应类似于:

let
  Source = Sql.Database("XXX", "YYY"),
  dbo_tblTest = Source{[Schema="dbo",Item="tblTest"]}[Data],
  #"Added Custom" = Table.AddColumn(dbo_tblTest, "Custom", each Text.Start([Item],5)),
  #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Grouped", each _, type table}}),
  Custom2 = Table.TransformColumns(#"Grouped Rows", {{"Grouped", (groupedTable) =>
      let
          #"Sorted Rows" = Table.Sort(groupedTable,{{"Test_Stop", Order.Descending}}),
          #"Kept First Rows" = Table.FirstN(#"Sorted Rows",10)
      in
          #"Kept First Rows"}}),
  #"Removed Other Columns1" = Table.SelectColumns(Custom2,{"Grouped"}),
  #"Expanded Grouped" = Table.ExpandTableColumn(#"Removed Other Columns1", "Grouped", Table.ColumnNames(#"Added Custom"))
in
  #"Expanded Grouped"

这篇关于Power Query 循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！