I have table containing the following information:

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

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.

CodePudding user response：

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"

CodePudding user response：

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"