Column in Group must contain a specific value

[LloydW]

Hi All,

I am new to the board and to Power Query but I am already hooked!  I have a table with the following data.

My constraint is that for each Batch ID, the Allocation Rule column must contain ONE "Non Dominant" allocation rule.  In the data above, Batch ID 1 is correct and Batch ID 2 is incorrect because it does not contain a "Non Dominant" allocation rule.

My expected results are below

I was able to solve this for this small data set, however, I will have to process thousands of batches and I think my "brute force" solution will take forever so I am looking for a more elegant solution.  My code is below

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Batch ID", Int64.Type}, {"Trans ID", Int64.Type}, {"Allocation Rule", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Batch ID"}, {{"Count", each _, type table [Batch ID=nullable number, Trans ID=nullable number, Allocation Rule=nullable text]}}),
    Custom1 = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([Count],"Has ND AR", each if pqVLOOKUP([Allocation Rule], Table8, 2, false) = "#N/A" then null else 1)),
    Custom2 = Table.AddColumn(Custom1, "Fill", each Table.FillDown([Custom],{"Has ND AR"})),
    #"Expanded Fill" = Table.ExpandTableColumn(Custom2, "Fill", {"Trans ID", "Allocation Rule", "Has ND AR"}, {"Fill.Trans ID", "Fill.Allocation Rule", "Fill.Has ND AR"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Fill",null,0,Replacer.ReplaceValue,{"Fill.Has ND AR"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Allocation Rule", each if ([Fill.Has ND AR] + [Fill.Trans ID]) = 1 then "Non Dominant" else [Fill.Allocation Rule]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "Custom", "Fill.Allocation Rule", "Fill.Has ND AR"})
in
    #"Removed Columns"

the fx pqVLOOKUP above was something I found on the Excel Guru board and the code is below

let pqVLOOKUP = (lookup_value as any, table_array as table, col_index_number as number, optional approximate_match as logical ) as any =>
let
/*Provide optional match if user didn't */
matchtype =
if approximate_match = null
then true
else approximate_match,

/*Get name of return column */
Cols = Table.ColumnNames(table_array),
ColTable = Table.FromList(Cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ColName_match = Record.Field(ColTable{0},"Column1"),
ColName_return = Record.Field(ColTable{col_index_number - 1},"Column1"),

/*Find closest match */
SortData = Table.Sort(table_array,{{ColName_match, Order.Descending}}),
RenameLookupCol = Table.RenameColumns(SortData,{{ColName_match, "Lookup"}}),
RemoveExcess = Table.SelectRows(RenameLookupCol, each [Lookup] <= lookup_value),
ClosestMatch=
if Table.IsEmpty(RemoveExcess)=true
then "#N/A"
else Record.Field(RemoveExcess{0},"Lookup"),

/*What should be returned in case of approximate match? */
ClosestReturn=
if Table.IsEmpty(RemoveExcess)=true
then "#N/A"
else Record.Field(RemoveExcess{0},ColName_return),

/*Modify result if we need an exact match */
Return =
if matchtype=true
then ClosestReturn
else
if lookup_value = ClosestMatch
then ClosestReturn
else "#N/A"
in Return
in pqVLOOKUP

Any suggestions would be greatly appreciated!!

Thanks in advance

LloydW

[wdx223_Daniel · Accepted solution]

NewStep=let col=Table.ColumnNames(PreviousStepName) in Table.Combine(Table.Group(PreviousStepName,"Batch ID",{"n",each let a=if List.Contains([Allocation Rule],"Non Dominate") then [Allocation Rule]{0} else "Non Dominate",b=Table.ToColumns(_) in Table.FromColumns(List.FirstN(b,2)&{{a}&List.Skip(b{2})},col)})[n])

[LloydW]

Daniel that is AMAZING!  worked perfectly!  Thank you so much!!