Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

TransformColumn error, cant apply Field access to type text

I have a column with Text and with numbers, my goal is, if its a number then Null else kept the Text value.

 

the Column type is mixed BUT if i do an "add Column" and use  if Value.Is([Value], type text) then [Value] else null this  works as new column BUT id rather transform in place.

 

I have tried these methods and they both return same error message above. can you assist with proper syntax. thanks.(screenshot of data below)

 

= Table.TransformColumns(#"Filled Up1", { "Value", 
each  if Value.Is([Value], type text ) then [Value] else null })
= Table.TransformColumns(#"Filled Up1", { "Value", 
each let field = _  in if Value.Is( field , type text ) then [Value] else null })

 

 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    Use this

    Table.ReplaceValue(#"Filled Up1", each [Value], each if [Value] is text then [Value] else null, Replacer.ReplaceValue, {"Value"})

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Use this

    Table.ReplaceValue(#"Filled Up1", each [Value], each if [Value] is text then [Value] else null, Replacer.ReplaceValue, {"Value"})