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

Debugging bulk type change

My data source looks like this

smpa01_0-1670353121983.png

I want to perform a bulk change of data type for all columns with the following condition

 

try Number.FromText(columns) otherwise 0

 

I tried like this

 

let
    src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LczBDQAhCETRXjjPJgIqUIux/zYW0Rtv8sNa5ODpBOIGGUYbOYWdASyzLODecxHM0FfEK6IcOq6tlxWu52mH8y20cVrNS1/eH+39Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [jan = _t, feb = _t]),
    ct = Table.TransformColumnTypes(src,{{"jan", type text}, {"feb", type text}}),
    cols = Table.ColumnNames(ct),
    def = List.Transform(cols, each{_, (x)=> try Number.FromText(x) otherwise 0, type number } ),
    Custom1 = Table.TransformColumnTypes(ct,def)
in
    Custom1

 

But it did not work. I am getting the following error

smpa01_1-1670353276738.png

 

@AlexisOlson @ImkeF @wdx223_Daniel @Anonymous @CNENFRNL 

Thank you in advance.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Use Table.TransformColumns instead of Table.TransformColumnTypes in your last line.

 

Here's how you can do the unpivoted version.

let
    src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LczBDQAhCETRXjjPJgIqUIux/zYW0Rtv8sNa5ODpBOIGGUYbOYWdASyzLODecxHM0FfEK6IcOq6tlxWu52mH8y20cVrNS1/eH+39Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [jan = _t, feb = _t]),
    ct = Table.TransformColumnTypes(src,{{"jan", type text}, {"feb", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(ct, {}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns", each [Value], each try Number.FromText([Value]) otherwise 0, Replacer.ReplaceValue, {"Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value", {{"Value", Int64.Type}})
in
    #"Changed Type"

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
Super User

try this code in a new step

NewStep=Table.TransformColumns(PreviousStepName,{},each try Number.From(_) otherwise 0)

AlexisOlson
Super User
Super User

Use Table.TransformColumns instead of Table.TransformColumnTypes in your last line.

 

Here's how you can do the unpivoted version.

let
    src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LczBDQAhCETRXjjPJgIqUIux/zYW0Rtv8sNa5ODpBOIGGUYbOYWdASyzLODecxHM0FfEK6IcOq6tlxWu52mH8y20cVrNS1/eH+39Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [jan = _t, feb = _t]),
    ct = Table.TransformColumnTypes(src,{{"jan", type text}, {"feb", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(ct, {}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns", each [Value], each try Number.FromText([Value]) otherwise 0, Replacer.ReplaceValue, {"Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value", {{"Value", Int64.Type}})
in
    #"Changed Type"

Thanks @AlexisOlson @wdx223_Daniel 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
AntrikshSharma
Super User
Super User

@smpa01 What is the purpose of the last row? Why didn't you filter it?

@AntrikshSharma  because the data is horrible and it may look like this. I need the data engineering to be done accurately.

 

smpa01_0-1670357320351.png

 

I guess I can do this but the question at this point will be how do I use to replace the content of the value column conditionally

 

try Number.From([Value]) otherwise 0 in this column 

 

 

 

let
    src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY3LDcAgDEN34VgZiRA+ySyI/ddoTCkScvzswFrJIMMSkhTUPmN44qSNSJxWIHWcvBxaIa2Frxiu1MvN/bapOedDXftHJ3f0dhWm/LPBhO328yJsTWb9sszX4g76/QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [jan = _t, feb = _t, mar = _t]),
    #"Added Index" = Table.AddIndexColumn(src, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

 

smpa01_1-1670357566508.png

I know I can create a conditional column but I am just curious how to use try statement in Table.Replace

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.