Power Query Looping

Question

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"

To get:

1. Test_ID --- Item --- Test_Start --- Test_Stop --- Custom
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 --- 8/21/2014 20:47 --- 6/10/2015 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

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"

To get:

1. Test_ID --- Item --- Test_Start --- Test_Stop --- Value
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 --- 7/23/2015 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

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?

Solution

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.

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.

Your query should be something like:

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"