Forum Discussion
Expression.Error] We cannot convert a value of type List to type Record.
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 "
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"
4 Replies
- BA_Pete
Super User
Hi Anonymous ,
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
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 Anonymous ,
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
