Data type error

[Rohit001]

Expression.Error: We cannot convert a value of type Record to type Number.
Details:
Value=[Record]
Type=[Type]

How to remove the error. I want to replace the NA value with a random number between its above and below value hee i used this code 

let
ReplaceNullWithValue = (value, above, below) =>
if value = null then
if above <> null and below <> null then Number.RandomBetween(above, below)
else if above <> null then above
else if below <> null then below
else null
else
Text.From(value)
in
ReplaceNullWithValue([Value], try [Value]{[Index]-1}, try [Value]{[Index]+1})

 

please help me

[AmiraBedh]

Can you share your pbix file ?

[AmiraBedh]

Try the following :

let
    Source = ..., // Your data source here
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    ReplaceNullWithValue = (value, above, below) =>
        if value = null then
            if above <> null and below <> null then Number.RandomBetween(above, below)
            else if above <> null then above
            else if below <> null then below
            else null
        else
            try Number.From(value) otherwise value,
    #"Custom Column" = Table.FromRecords(
        Table.TransformRows(#"Added Index", each 
            Record.TransformFields(_,{
                {"Value", each ReplaceNullWithValue(_, try #"Added Index"{[Index]-1}[Value] otherwise null, try #"Added Index"{[Index]+1}[Value] otherwise null)}
            })
        )
    )
in
    #"Custom Column"

[Rohit001]

New error :

Expression.Error: The field 'Value' of the record wasn't found.
Details:
Name=Sheet1
Data=[Table]
Item=Sheet1
Kind=Sheet
Hidden=FALSE
Index=0 

[parry2k]

You have to change the codition to NA instead of null, these are two different values. 

[parry2k]

@Rohit001 I think you need this:

let
ReplaceNullWithValue = (value, above, below) =>
if value = null then
if above <> null and below <> null then Number.RandomBetween(above, below)
else if above <> null then above
else if below <> null then below
else null
else
Text.From(value)
in
ReplaceNullWithValue([Value], try #"Added Index"{[Index]-1}[Value] otherwise null, try #"Added Index"{[Index]+1}[Value] otherwise null)

change #"Added Index"  name with the previous step when you are adding this new custom column

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

[Rohit001]

But it still showing a null value

[Rohit001]