cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
FeRTeK
Regular Visitor

Expression.Error: We cannot apply field access to type Number

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 ) 

 

=2023-09-05.jpg

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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

ppm1
Solution Sage
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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.