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"
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"thank you Greg_Deckler and sanimesa
Greg, your answer fixed it in my sample data and my actual data. I wasn't considering nulls and "" were not the same. I still would like to know why "" and not null would be flagged by PowerBI when refreshing for my own understanding, but your solution is definitely better because it's probably best practice to never have PowerBI tell me that I have errors.