Forum Discussion

Rohit001's avatar
Rohit001
Helper I
3 years ago

Data type error

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

Need Help

7 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    3 years ago

    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's avatar
      Rohit001
      Helper I
      3 years ago

      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's avatar
    parry2k
    Super User
    3 years ago

    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

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

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