cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Hello, I have two columns and if the value in the second column is in the first column, if it does not pass itself, if it contains values smaller than itself, if it contains values greater than itself, I want it to give the minimum value among the values larger than itself in the list, otherwise return null

but when I create a condition like the following, I get a type error when I want to get the values in the list larger than the data in column B for List.Skip. I couldn't get past this part. What can be done instead.

Table.AddColumn(TransformColumnTypes, "C", each if List.Contains(Expression.Evaluate("{"&[A]&"}"), [B] ) then [B] else if [B] > List.Max(Expression.Evaluate("{"&[A]&"}"),1) then List.Min(Expression.Evaluate("{"&[A]&"}"),1) else if [B] < List.Max(Expression.Evaluate("{"&[A]&"}"),1) then List.Min(List.Skip(Expression.Evaluate("{"&[A]&"}"), each _ < [B])) else null )

=

1 ACCEPTED SOLUTION
Super User

List.Skip(Expression.Evaluate("{"&[A]&"}"), (x)=>x < [B])

2 REPLIES 2
Super User

List.Skip(Expression.Evaluate("{"&[A]&"}"), (x)=>x < [B])

Solution Sage

Not sure I got all your logic correct, but you can simplify it with this approach (add this code to the pop-up custom column dialog).

``````let
inputvalue = [B],
inputlist = List.Transform(Text.Split([A], ","), each Number.From(_)),
greaterthanlist = List.Select(inputlist, each _ >= inputvalue),
listmax = List.Max(inputlist),
listmin = List.Min(inputlist),
result = if List.Contains(inputlist, inputvalue) then inputvalue else if inputvalue > listmax then listmin else if inputvalue < listmax then List.Min(greaterthanlist) else null
in
result``````

Pat

Microsoft Employee