Forum Discussion
PowerBI, How to handle errors and text Values
Hi Everyone, I'm new to PowerBI and need to self-learn, and I have a couple questions. So at my firm, due to its practices and constraints, a lot of data is stores across many Excel files. The fi...
ImkeF or edhans probably know better but maybe try replacing the errors with null
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVC7DsIwDPyVKDOCJm15jLwREguMVYfQB1QKcRVKK/4eJ0oDQjD4FMf23dlJQud0QE8gc7IpzoQHPMCcxSGiGokv3Jm+EjQRda2hFZKUGm5koYumwXKEMabpIKELfMWWKWCI3MQwRgxdW2yzucp10RFdZKCySlbqYqeXvaWDeL4tBT8tMUvUC5jplXmbT8Yir2s1rb2/WxyfQmF1aknWXpLxmROI3PzHNk5y0xveC+UNT/hPw3u4KtIALn2vQeXvFRzX1p9uzH0xdPb7861EW+WGpQQpoSOPmqbpCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, #"Share Count" = _t, #"Last trade price" = _t, #"Low Projection Multiple" = _t, #"Mid Projection Multiple" = _t, #"High Projection Multiple" = _t, #"Company Future Low Projection Multiple" = _t, #"Company Future Mid Projection Multiple" = _t, #"Company Future High Projection Multiple" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Share Count", Int64.Type}, {"Last trade price", Int64.Type}, {"Low Projection Multiple", type text}, {"Mid Projection Multiple", type text}, {"High Projection Multiple", type text}, {"Company Future Low Projection Multiple", type text}, {"Company Future Mid Projection Multiple", type text}, {"Company Future High Projection Multiple", type text}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Mid Projection Multiple", type number}, {"Low Projection Multiple", type number}, {"High Projection Multiple", type number}, {"Company Future Low Projection Multiple", type number}, {"Company Future Mid Projection Multiple", type number}, {"Company Future High Projection Multiple", type number}}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Company Future High Projection Multiple", null}}), #"Replaced Errors1" = Table.ReplaceErrorValues(#"Replaced Errors", {{"Company Future Mid Projection Multiple", null}}), #"Replaced Errors2" = Table.ReplaceErrorValues(#"Replaced Errors1", {{"Company Future Low Projection Multiple", null}}), #"Replaced Errors3" = Table.ReplaceErrorValues(#"Replaced Errors2", {{"High Projection Multiple", null}}), #"Replaced Errors4" = Table.ReplaceErrorValues(#"Replaced Errors3", {{"Mid Projection Multiple", null}}), #"Replaced Errors5" = Table.ReplaceErrorValues(#"Replaced Errors4", {{"Low Projection Multiple", null}}), #"Replaced Errors6" = Table.ReplaceErrorValues(#"Replaced Errors5", {{"Share Count", null}}) in #"Replaced Errors6"
AnonymousI would do the following:
1. Load the data as is. Power Bi will identify those attributes as text.
2. Create corresponding numeric columns for each amount column using the DAX formula of ISNUMERIC, VALUE or CONVERT.
You can use the BLANK() to product a blank value where there is space/empty values.