Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
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])
Daniel that is AMAZING! worked perfectly! Thank you so much!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.