Forum Discussion
Solved
Power Query - Matching code in rows for if then calculation
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 eval...
Hi 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
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"SergeiBaklan 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 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"- This works perfectly, thanks again!
SergeiBaklan this works great, thank you!
jtooke1 , you are welcome
- You seem to have implemented SergeiBaklan proposal but marked mine as Best response...
Lorenzo wrote:
You seem to have implemented SergeiBaklan proposal but marked mine as Best response...No problem, doesn't matter. They are all workable.
