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 files are human friendly to analyze but are proving to be difficult when trying to find automated solutions using tools like PowerBI. I need to connect powerBi to a specific excel file where data comes in both numeric and text format.
Please see the following made up data below. It's just a sample and there any more columns and rows just like the below data.
| Company | Share Count | Last trade price | Low Projection Multiple | Mid Projection Multiple | High Projection Multiple | Company Future Low Projection Multiple | Company Future Mid Projection Multiple | Company Future High Projection Multiple |
| A | Sold Feb 2020 | 153 | n/a | n/a | n/a | Hold for approval from Brett | 4 | 6 |
| B | 50 | 101 | 2 | 2.5 | 3 | 4 | 5.5 | Andrew reconciling |
| C | Sold May 2020 | 150 | n/a | n/a | n/a | 1.5 | 2 | 2.5 |
| D | 25 | 114 | 2.5 | 4 | 5 | 6 | Hold for approval from Ryan | 8 |
| E | 150 | 129 | 2 | 4 | 6 | 2 | 2.5 | 3.5 |
| F | Sold Jan 2020 | 172 | n/a | n/a | n/a | John to respond | 1.5 | 2.5 |
| G | 50 | 162 | 1.5 | 3 | 5 | 3 | 4 | David to follow up |
So there is some structure, but effectively any value can be entered into the columns because both numbers and human notes are in here. I'll need to produce reports off this data where the numbers are in a very specific format, but PowerBI won't let me change the format because some columns have both text and numeric values, so the format is only text and can't be changed to custom.
If I replace all the text values as blanks ("") , then I will be fine to produce the reports I need. Please see my attempts and questions below.
Question 1) How error locating works?
My current method of solving this is to force change all the column values with both numbers and text into type number in power query. This will cause all the text values to be now errors:
Now I can replace all the errors at the end with blanks and get the below. This is fine for all my reports.
However, when I load back into PowerBI, I get the following message:
I clicked view errors, and the table I get is this:
The table is empty? I know this error table is made off of selecting each row with an error in at least one of the columns. But I replaced all the errors at the end, and then after that loaded the data back into PowerBI. How come PowerBI is telling me there is an error after I already handled them? When I investigate, the "6 errors" I get is every company but company E because that's the only one without a text value in any column, so nothing gets errored out ever.
(in the below picture, I just replaced the source data in "Errors in Sheet1" with "Query1" to investigate),
Do you think there is some hidden error somewhere? Is forcing errors like this a bad approach?
Question 2) Is there any way to replace any text value in a column with "" but don't touch any numeric values? This way I won't have to force any errors. I know there's replace text option, but I have to give a specific text value, and in my real data set I'm working with effectively infinitly many text values (the text values are little notes) in aout 50 columns.
Thanks for reading, I know it was a long post!
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"
4 Replies
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.
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.
and any simpler way to avoid this error? via options settings, etc ? I just installed the latest version and it appears every time I want to update.
