Feb 01 2022 08:40 AM
Hi Everyone,
I am looking to use Power Query to create a custom column based on a column value across matching codes in separate rows. For example, whenever rows have the same "Code", I want to evaluate all "Compliance" values across the rows to determine an "Overall Compliance" column. I have included some sample data below, where I am trying to achieve Overall Compliance. Overall Compliance should only be "Yes" when all values for Compliance across each Code are Yes. Overall Compliance should be No if any value across the Code is No.
Code | Compliance | Overall Compliance |
123 | Yes | Yes |
123 | Yes | Yes |
123 | Yes | Yes |
456 | Yes | No |
456 | No | No |
456 | Yes | No |
789 | Yes | No |
789 | No | No |
789 | No | No |
Is there a way to do this with Power Query?
Thanks!
Feb 01 2022 08:56 AM
As variant
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Code"},
{{"Count", each _, type table [Code=number, Compliance=text]}}),
#"Added Custom" = Table.AddColumn(
#"Grouped Rows",
"Overall Compliance",
each
if List.AllTrue( List.Transform( [Count][Compliance], each _ = "Yes" ))
then "Yes"
else "No" ),
#"Expanded Count" = Table.ExpandTableColumn(
#"Added Custom",
"Count",
{"Compliance"}, {"Compliance"})
in
#"Expanded Count"
Feb 01 2022 09:08 AM
SolutionHi @jtooke1
Another approach assumind data in Excel Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedTypes = Table.TransformColumnTypes(Source,
{{"Code", Int64.Type}, {"Compliance", type text}}
),
GroupedRows = Table.Group(ChangedTypes, {"Code"},
{"CodeTable", each
let
AnyNo = Table.SelectRows(_, each Text.Lower([Compliance]) = "no")
in
Table.AddColumn(_, "Overall Compliance", each
if Table.IsEmpty(AnyNo) then "Yes" else "No",
type text
),
type text
}
),
CombinedTables = Table.Combine(GroupedRows[CodeTable])
in
CombinedTables
Feb 01 2022 10:12 AM
@Sergei Baklan this works great, thank you!
Feb 01 2022 11:30 AM
Feb 01 2022 11:45 AM
Yes, at least from performance point of view better to work with tables, not lists. One more variant
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Code"},
{{"Count", each _, type table [Code=number, Compliance=text]}}),
#"Add Overall" = Table.AddColumn(
#"Grouped Rows",
"Overall Compliance",
each
if Table.MatchesAllRows( [Count], each [Compliance] = "Yes" )
then "Yes"
else "No",
type text ),
#"Expand it" = Table.ExpandTableColumn(
#"Add Overall",
"Count",
{"Compliance"}, {"Compliance"})
in
#"Expand it"
Feb 01 2022 11:45 AM
@jtooke1 , you are welcome
Feb 01 2022 11:46 AM
@L z. wrote:
You seem to have implemented @Sergei Baklan proposal but marked mine as Best response...
No problem, doesn't matter. They are all workable.
Feb 01 2022 01:33 PM
@Sergei Baklan I have a follow up question around this, if I am looking to adjust the code to produce the below results, is that possible also? Essentially, I am looking to add a level where if any Compliance for each Code = No, then Overall Compliance will equal "Partial", and where Compliance all is equal to one value then Overall Compliance will equal that value, no matter what it may be.
ode | Compliance | Overall Compliance |
123 | Yes | Yes |
123 | Yes | Yes |
123 | Yes | Yes |
456 | Yes | Partial |
456 | No | Partial |
456 | Yes | Partial |
789 | Yes | Partial |
789 | No | Partial |
789 | No | Partial |
1234 | N/A | N/A |
1234 | N/A | N/A |
1234 | N/A | N/A |
856 | No | No |
856 | No | No |
856 | No | No |
Feb 01 2022 09:57 PM
Feb 02 2022 01:11 AM
That's practically the same
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Code"},
{{"Count", each _, type table [Code=number, Compliance=text]}}),
#"Add Overall" = Table.AddColumn(
#"Grouped Rows",
"Overall Compliance",
each
if Table.MatchesAllRows( [Count], each [Compliance] = "Yes" )
then "Yes"
else
if Table.MatchesAllRows( [Count], each [Compliance] = "No" )
then "No"
else
if Table.MatchesAllRows( [Count], each [Compliance] = "N/A" )
then "N/A"
else "Partial",
type text ),
#"Expand it" = Table.ExpandTableColumn(
#"Add Overall",
"Count",
{"Compliance"}, {"Compliance"})
in
#"Expand it"
Feb 02 2022 05:28 AM
Feb 02 2022 01:16 PM
@jtooke1 , glad to help
Feb 01 2022 09:08 AM
SolutionHi @jtooke1
Another approach assumind data in Excel Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedTypes = Table.TransformColumnTypes(Source,
{{"Code", Int64.Type}, {"Compliance", type text}}
),
GroupedRows = Table.Group(ChangedTypes, {"Code"},
{"CodeTable", each
let
AnyNo = Table.SelectRows(_, each Text.Lower([Compliance]) = "no")
in
Table.AddColumn(_, "Overall Compliance", each
if Table.IsEmpty(AnyNo) then "Yes" else "No",
type text
),
type text
}
),
CombinedTables = Table.Combine(GroupedRows[CodeTable])
in
CombinedTables