电力查询循环

Question

我使用Power Query提取在过去一个月中测试的所有唯一项类型：

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_Start- Test_Stop -自定义
2. 2585048 -B 1846-6-02-1/14/201412:46-6/25/201514:28- B1846
3. 2589879 -B 1843-5-05-12/23/201316:46-6/25/201514:19- B1843
4. 2633483 -B 1907-1-04-8/21/201420:47- 6/10/2015 6:20 - B1907
5. 2638786 -B 1361-2-04-6/13/201314:21-6/16/201514:15- B1361
6. 2675663 -B 1345-2-02-5/23/201418:39-6/25/201521:27- B1345

接下来，我想使用Power查询为Query1中列出的每一项类型提取过去的10次测试，而不考虑时间段。我想出了如何分别为项目类型提取过去的10个测试，但不是在一个查询中将所有测试放在一起。

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 -B 1846-6-02- 7/23/2015 12:48 -7/23/201512:57- 43725341
3. 11716432 -B 1846-1-21- 7/23/2015 10:23 - 7/23/2015 10:29 - 43724705
4. 11715802 -B 1846-1-21- 7/23/2015 9:28 - 7/23/2015 10:29 - 43724720
5. 11715505 -B 1846-1-21- 7/23/2015 8:59 - 7/23/2015 9:06 - 43724675
6. 11715424 -B 1846-1-21- 7/23/2015 8:36 - 7/23/2015 8:59 - 43724690
7. 11713680 -B 1846-1-55- 7/23/2015 5:50 - 7/23/2015 6:07 - 43725239
8. 11691169 -B 1846-6-04-7/20/201522:47-7/22/201520:18- 43642835
9. 11690466 -B 1846-6-04- 7/20/2015 21:30 -7/22/201518:41- 43642729
10. 11701183 -B 1846-1-140
11. 11701184 --B 1846-6-04-7/21/201320:35-7/21/201520:46- 43667359

是否可以使用Power查询在一个查询中提取所有需要的数据？如果没有，是否可以使用VBA和Power查询来完成？

Answer 1

在Power Query中，如果您在考虑如何循环，您通常会发现一个高级库函数，它可以满足您的需要。在这种情况下，它是分组的。

按某些键对表进行分组，在您的示例中，第一个表的Custom列。您可以使用Table.TransformColumns将“保持过去10”逻辑重写为在每个分组表中应用的函数，然后将分组表展开到一个平面表中。

您的查询应该类似于：

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"