我使用 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"
To get:
- Test_ID --- 项目 --- Test_Start --- Test_Stop --- 自定义
- 2585048 --- B1846-6-02 --- 2014/1/14 12:46 --- 2015/6/25 14:28 --- B1846
- 2589879 --- B1843-5-05 --- 2013/12/23 16:46 --- 2015/6/25 14:19 --- B1843
- 2633483 --- B1907-1-04 --- 8/21/2014 20:47 --- 6/10/2015 6:20 --- B1907
- 2638786 --- B1361-2-04 --- 2013/6/13 14:21 --- 2015/6/16 14:15 --- B1361
- 2675663 --- B1345-2-02 --- 2014/5/23 18:39 --- 2015/6/25 21:27 --- B1345
接下来,我想使用 Power Query 为 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"
To get:
- Test_ID --- 项目 --- Test_Start --- Test_Stop --- 值
- 11717643 --- B1846-6-02 --- 7/23/2015 12:48 --- 7/23/2015 12:57 --- 43725341
- 11716432 --- B1846-1-21 --- 7/23/2015 10:23 --- 7/23/2015 10:29 --- 43724705
- 11715802 --- B1846-1-21 --- 7/23/2015 9:28 --- 7/23/2015 10:29 --- 43724720
- 11715505 --- B1846-1-21 --- 7/23/2015 8:59 --- 7/23/2015 9:06 --- 43724675
- 11715424 --- B1846-1-21 --- 7/23/2015 8:36 --- 7/23/2015 8:59 --- 43724690
- 11713680 --- B1846-1-55 --- 7/23/2015 5:50 --- 7/23/2015 6:07 --- 43725239
- 11691169 --- B1846-6-04 --- 7/20/2015 22:47 --- 7/22/2015 20:18 --- 43642835
- 11690466 --- B1846-6-04 --- 7/20/2015 21:30 --- 7/22/2015 18:41 --- 43642729
- 11701183 --- B1846-1-140 --- 2015/7/21 21:34 --- 2015/7/21 22:24 --- 43667358
- 11701184 --- B1846-6-04 --- 2013/7/21 20:35 --- 2015/7/21 20:46 --- 43667359
是否可以使用 Power Query 在一次查询中提取所有需要的数据?如果没有,是否可以使用 VBA 和 Power Query 来完成它?