Expression.Error] We cannot convert a value of type List to type Record.

[Niiru1]

I have a table of text values I need to group / find the "Succeeded" % and I was hoping to count each "Succeeded" and "Resolved"

My aim is to get the % of each column succeeded at each date e.g. CALCUALTE(COUNT([1.1 Attributions Monitor Check])="Succeeded")) / ALLSELECTED ([1.1 Attributions Monitor Check])  (11/12 = 91.67%) and then it would be 100% on other dates in each column.

Is this possible?

OVERALL AIM

Hoping to plot the %s on a line graph to show the success % over a period of time.

I have tried grouping 1 column using Query editor table.group but this only works for 1 column and when I enter another I get the "Expression.Error] We cannot convert a value of type List to type Record." error

Script I am using is:

let
Source = Excel.Workbook(Web.Contents("https://blank.sharepoint.com/teams/Project%20Report.xlsx"), null, true),
#"Daily Checks_Sheet" = Source{[Item="Daily Checks",Kind="Sheet"]}[Data],
#"Promoted Headers1" = Table.PromoteHeaders(#"Daily Checks_Sheet", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Date", type date}, {"1.1 Attributions Monitor Check", type text}, {"1.2 Timings Monitor Check", type text}, {"2.1 BI - Datasets Refresh Check", type text}, {"2.2 BI - Apps Refresh Check", type text}, {"2.3 PowerApps Check", type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Date", type date}, {"1.1 Attributions Monitor Check", type text}, {"1.2 Timings Monitor Check", type text}, {"2.1 BI - Datasets Refresh Check", type text}, {"2.2 BI - Apps Refresh Check", type text}, {"2.3 PowerApps Check", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Resolved Check", each [1.1 Attributions Monitor Check] & "-" & [1.2 Timings Monitor Check] & "-" & [#"2.1 BI - Datasets Refresh Check"] & "-" & [#"2.2 BI - Apps Refresh Check"] & "-" & [2.3 PowerApps Check]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Resolved Check", "Resolved Check ⚠"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Changed Type", {"1.1 Attributions Monitor Check"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"

[BA_Pete]

Hi @Niiru1 ,

I'm not sure what you're expecting Power Query to do here.

Your own post seems to describe the correct solution to the problem i.e. using a measure to divide the count of successful rows by the total rows in the date window. Are you just after help writing the measure correctly?

I assume you have a calendar table that maps dates to week numbers for the chart axis, so everything should be in place to produce your chart otherwise.

Pete

[Niiru1]

Yes I suppose a measure would work best but any previous attempts I have made at a measure just give me the 91% value but I'm unable to plot it along a date axis as it only gives me a value of 1 / 100% instead of the 91%

.

formula I have used is:

1.1 % Succeeded = 
VAR succeed1 = CALCULATE(COUNT('Daily Checks'[1.1 Attributions Monitor Check]),'Daily Checks'[1.1 Attributions Monitor Check]=="Succeeded")
VAR totalmonitor = CALCULATE(COUNT('Daily Checks'[1.1 Attributions Monitor Check]),'Daily Checks'[1.1 Attributions Monitor Check]<>BLANK())
RETURN
DIVIDE(succeed1,totalmonitor)

[BA_Pete]

Hi @Niiru1 ,

You're not going to get the correct values against a date axis as you only have one value per date in your table, so no total count to divide by.

You would need to have a period that contains at least two dates on the axis to create a group of rows that are not just that date row to divide by.

I'll have a look in more detail tomorrow and get back to you.

Pete

[BA_Pete]

Hi @Niiru1 ,

If you want to display the performance on a week axis, then use this measure:

_weeklyAttribs = 
VAR __weekCount =
CALCULATE(
    COUNT(dailyChecks[attribs]),
    ALLEXCEPT(dailyChecks, dailyChecks[week])
)
VAR __successCount =
CALCULATE(
    COUNT(dailyChecks[attribs]),
    dailyChecks[attribs] = "S",
    ALLEXCEPT(dailyChecks, dailyChecks[week])
)
RETURN
DIVIDE(__successCount, __weekCount, 0)

This gives me the following output:

You'll notice that using this measure will give you a correct 'running' value for incomplete weeks e.g. week 3.

As mentioned before, if you want to display your measures on a DATE axis, you will need a measure that creates a denominator group, such as 'last 7 days' etc.

Pete