如何使用电源查询计算组合结果并放在单独的表中

发布于 2025-02-08 20:17:39 字数 2652 浏览 2 评论 0原文

我的表包含以下信息：

主机名	测试名称	结果
host_01	test-01	Pass
host_01	test-02	fail>失败
host_01	test-03	n/a
HOST_01	TEST-04	PASS
HOST_01	TEST-05	PASS
host_02	test-01	Pass
host_02	test-02 test-02	fail> faff
host_02	Test-03	Fail
Host_02	Test-04	Pass

Now I want/need to transform this to the following result:

Host name	Num_of_Tests_per_Host	Num_Passed	Num_Failed	Num_N/A
Host_01	5	3	1	1
Host_02	4	2	2	null

我创建了查询以提取Disctinct主机列表并计算出不同的“测试名称 - 结果”的情况。但是我无法围绕如何合并这些查询来创建结果表。

这可能是“ Arggh ...我为什么没有想到！”解决方案，但似乎无法抓住它。

非常感谢。

原文

I have table containing the following information:

Host name	Test name	Result
Host_01	Test-01	Pass
Host_01	Test-02	Fail
Host_01	Test-03	N/A
Host_01	Test-04	Pass
Host_01	Test-05	Pass
Host_02	Test-01	Pass
Host_02	Test-02	Fail
Host_02	Test-03	Fail
Host_02	Test-04	Pass

Now I want/need to transform this to the following result:

Host name	Num_of_Tests_per_Host	Num_Passed	Num_Failed	Num_N/A
Host_01	5	3	1	1
Host_02	4	2	2	null

I created the queries to extract the disctinct host list and count the distinct "Test name - Result" occurances. But I can't get my head around how to merge these queries to create the results table.

This will probably be an "Arggh ... Why didn't I think of that!!!" solution, but can't seem to catch it.

Many thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一人独醉 2025-02-15 20:17:39

假设单元格内容为nulls而不是文字说null，

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Host name"},{
    {"Num_of_Tests_per_Host", each List.Count(List.Distinct([Test name])), type number},
    {"Num_Passed", each List.NonNullCount([Passed]), type number},
    {"Num_Failed", each List.NonNullCount([Failed]), type number},
    {"Num_N/A", each List.NonNullCount([#"N/A"]), type number}
})
in #"Grouped Rows"

Assuming the cell contents are nulls instead of text saying null,

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Host name"},{
    {"Num_of_Tests_per_Host", each List.Count(List.Distinct([Test name])), type number},
    {"Num_Passed", each List.NonNullCount([Passed]), type number},
    {"Num_Failed", each List.NonNullCount([Failed]), type number},
    {"Num_N/A", each List.NonNullCount([#"N/A"]), type number}
})
in #"Grouped Rows"

回复收藏 0 原文

秉烛思 2025-02-15 20:17:39

尝试一下：

let

//Change table name in next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Host name", type text}, 
        {"Test name", type text}, 
        {"Result", type text}}),

//Group by Host Name
//  Aggregate to get total of tests, Pass, Fail, N/A
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Host name"}, {
       {"Num_of_Tests_per_Host", each Table.RowCount(_), Int64.Type},
       {"Num_Passed", (t)=>List.Count(List.Select(t[Result], each _="Pass")), Int64.Type},
       {"Num_Failed", (t)=>List.Count(List.Select(t[Result], each _="Fail")), Int64.Type},
       {"Num_N/A", (t)=>List.Count(List.Select(t[Result], each _="N/A")), Int64.Type}
        })
in
    #"Grouped Rows"

Try this:

let

//Change table name in next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Host name", type text}, 
        {"Test name", type text}, 
        {"Result", type text}}),

//Group by Host Name
//  Aggregate to get total of tests, Pass, Fail, N/A
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Host name"}, {
       {"Num_of_Tests_per_Host", each Table.RowCount(_), Int64.Type},
       {"Num_Passed", (t)=>List.Count(List.Select(t[Result], each _="Pass")), Int64.Type},
       {"Num_Failed", (t)=>List.Count(List.Select(t[Result], each _="Fail")), Int64.Type},
       {"Num_N/A", (t)=>List.Count(List.Select(t[Result], each _="N/A")), Int64.Type}
        })
in
    #"Grouped Rows"