Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Rohit001
Helper I
Helper I

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})

 

 Screenshot 2023-08-06 185818.png

please help me

7 REPLIES 7
AmiraBedh
Super User
Super User

Can you share your pbix file ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
AmiraBedh
Super User
Super User

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"

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

But it still showing a null value

 

Screenshot 2023-08-06 234716.png

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.