Forum Discussion
Fill Up or Down or both by Batch ID
I have a table with the values below. On the left is the current state. On the right is where I need to get to. Effectively, I need to fill the "Bank Account" value on all records where the Batch ID is the same. If there is no Bank Account, nothing should happen. The catch for me is that the value could be on the first, last or any record in between so the fill is either up, down, or both up and down.
Appreciate any help or direction.
Thanks!!
NewStep=Table.Combine(Table.Group(PreviousStepName,{"Batch Index","Trans Index"},{"n",each let a=List.RemoveItems([Bank Account],{"",null}){0}? in Table.TransformColumns(_,{"Bank Account",each if _=null or _="" then a else _})},0,(x,y)=>Byte.From(x[Batch Index]<>y[Batch Index] or y[Trans Index]=1))[n])
4 Replies
- wdx223_Daniel
Community Champion
NewStep=Table.Combine(Table.Group(PreviousStepName,{"Batch Index","Trans Index"},{"n",each let a=List.RemoveItems([Bank Account],{"",null}){0}? in Table.TransformColumns(_,{"Bank Account",each a})},0,(x,y)=>Byte.From(x[Batch Index]<>y[Batch Index] or y[Trans Index]=1))[n])
This works PERFECTLY with the exception of transactions like the following (my apologies this scenario was not included in my original post):
Basically, only fill the null values if there are any.
Thank you again for your help!!
Best,
LloydW
- wdx223_Daniel
NewStep=Table.Combine(Table.Group(PreviousStepName,{"Batch Index","Trans Index"},{"n",each let a=List.RemoveItems([Bank Account],{"",null}){0}? in Table.TransformColumns(_,{"Bank Account",each if _=null or _="" then a else _})},0,(x,y)=>Byte.From(x[Batch Index]<>y[Batch Index] or y[Trans Index]=1))[n])