Looping in Power Query

0 votes

So, I'm attempting to use Power Query to pull all of the unique Item Types tested the last month, as given below.

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"

I'm getting this.

  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 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.

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"

I'm getting the following.

  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

 But I'm trying to do so in one query. Is it possible?

Jan 30 in Power BI by Shubham
• 13,290 points
269 views

1 answer to this question.

0 votes

In Power Query, you often find a higher-order library function that suffices your requirement. If you want to loop- in your case, you'll need "Grouping". It, basically, splits up a table by some key.

You can rewrite your "keep past 10" logic into a function that you apply within each grouped table using Table > TransformColumns > expand the grouped tables back out into one flat table.

Your query should look something like the following:

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"
answered Jan 30 by Upasana
• 8,530 points

Related Questions In Power BI

0 votes
1 answer

Dax query in power bi

Here is my table before I have ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 14,070 points
75 views
0 votes
1 answer

Select Columns from table instead of removing afterwards in Power Query

Try this. let db = Sql.Databases("sqlserver.database.url"){[Name="DatabaseName"]}[Data], Sales_vDimCustomer = ...READ MORE

answered Oct 31, 2018 in Power BI by Shubham
• 13,290 points
188 views
+1 vote
1 answer

How to replace null values with custom values in Power BI(power query editor)?

Hi Nithin, To fill or replace the null or ...READ MORE

answered Mar 8 in Power BI by Cherukuri
• 25,580 points
3,439 views
0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18 in Power BI by Cherukuri
• 25,580 points
415 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
343 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
188 views
0 votes
1 answer

How can I search for multiple strings?

A simple solution is this: List.ContainsAny(Text.SplitAny("This is a test ...READ MORE

answered Oct 24, 2018 in Power BI by Upasana
• 160 points
145 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 13,290 points
140 views
0 votes
1 answer

Cumulative row data over last 12 months in Power Query

Basically, what you do is,  Add an Index,  Group ...READ MORE

answered Apr 1 in Power BI by Upasana
• 8,530 points
212 views
0 votes
1 answer

How can i see my plugin name in the Power BI Visual plugin List ?

there is a couple of errors in your ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 8,530 points
48 views