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 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.