Forum Discussion
Trying to select rows before expanding data from multiple tables
Here is what I have so far:
I am trying to select the rows in each table in the Data column where the Amount column in the inner table is over the cutoff amount. However, I am not sure if this is possible. Here is what I am trying:
#"Select Over Cutoff" = Table.TransformColumns(#"PreviousStepName", {{"Data", each Table.SelectRows(_, each [Amount] > #"PreviousStepName"[CutOff]>)}})
The thing is, if I just put a regular number in, then it will work:
#"Select Over Cutoff" = Table.TransformColumns(#"PreviousStepName", {{"Data", each Table.SelectRows(_, each [Amount] > 500)}})
This would be good if I needed the value 500 every time. It may just not be possible to access the values in the cutoff column, because any column referenced inside the SelectRows functions is limited to the inner table.
Hi
with Table.ReplaceValue
= Table.ReplaceValue(#"PreviousStepName",each [CutOff],"",(x,y,z)=>Table.SelectRows(x,each [Amount] > y),{"Data"})Stéphane
3 Replies
Hi
with Table.ReplaceValue
= Table.ReplaceValue(#"PreviousStepName",each [CutOff],"",(x,y,z)=>Table.SelectRows(x,each [Amount] > y),{"Data"})Stéphane
Thank you. I don't understand it, but it works. I understand the concept of a function, but I am not sure how it is getting called or where the parameters come from. Anyway, thank you again.
x = the [Data] column of each row (a table)
y = the [CutOff] column of each row (a value)
z = "" (not used in function)
Stéphane
