Forum Discussion
Resolver IICalculate Open Customer Defects and Open Customer Issues
Resolver IIIJeevanMallya let me remind you of a few things; this is free support and no one on here is obligated to help you. So you should be more clear with your requirements, and more gracious when someone is trying to help you.
Resolver IINo idea what was not gracious. I had clearly provided the Expected Table in the requirements earlier.
I was able to figure out anf fix by using the power query
// Split REPORTED_ISSUE_KEY_CUMULATIVE into lists
AddReportedList = Table.AddColumn(
#"Removed Columns",
"ReportedList",
each if Text.Length([REPORTED_ISSUE_KEY_CUMULATIVE]) > 0 then
Text.Split([REPORTED_ISSUE_KEY_CUMULATIVE], ",")
else
{}
),
// Split CLOSED_ISSUE_KEY_CUMULATIVE into lists
AddClosedList = Table.AddColumn(
AddReportedList,
"ClosedList",
each if Text.Length([CLOSED_ISSUE_KEY_CUMULATIVE]) > 0 then
Text.Split([CLOSED_ISSUE_KEY_CUMULATIVE], ",")
else
{}
),
// Compute OPEN_ISSUES by removing closed issues from reported issues
AddOpenIssues = Table.AddColumn(
AddClosedList,
"OPEN_ISSUES",
each if List.IsEmpty([ClosedList]) then
[REPORTED_ISSUE_KEY_CUMULATIVE]
else
Text.Combine(List.RemoveMatchingItems([ReportedList], [ClosedList]), ",")
),
// Remove intermediate columns
RemoveIntermediateColumns = Table.RemoveColumns(
AddOpenIssues,
{"ReportedList", "ClosedList"}
)
in
RemoveIntermediateColumns
- bchager
JeevanMallya This should do it. Take a look at rows 6 down in the advanced editor for the Table query.
- JeevanMallya
Thank you