Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am new to Business Intelligence space and PowerBI. So, please forgive me if this question sounds stupid.
I am working on a Dataset(CSV) that has around 40 columns and and 400 rows and I can not afford loose data in the data cleaning process.
I am facing the problem with the columns that have numeric values as well as very few charater values. I can not treat these columns as character data types because I need to perform aggrgations on these columns. For example I have a column that has most of the numeric values, but it alos has few N/A and '-' for some rows I can not remove these because they have specific business meaning associated with them. This because when I group the data, I want a user to be able to see these charater & numeric values a in a single report like follwing -
Summarry report -
Year Revenue Quality
2017 Not reported 90%
2017 $250,000 95%
2016 $100,000 N/A
2015 $200,000 85%
Can somebody please advice me on how to acheive this?
Thanks!
Solved! Go to Solution.
Hi @BILearner,
Maybe we could add a new column, which will have 0 instead of "n/a" and "-". Then we could do some math operations in this new column. Finally, we could use the old and new columns to output a proper result.
Result = VAR sumq = SUM ( Table1[QuantityInNum] ) RETURN IF ( sumq = 0 && MIN ( 'Table1'[Quantity] ) = "n/a", "n/a", IF ( sumq = 0 && MIN ( 'Table1'[Quantity] ) = "-", "-", sumq ) )
Notes: 1. All the data of 2016 is "N/A" and all the data of 2014 is "-", right?
2. The type of result measure has to be "Text" because of the characters.
Best Regards
Dale
Hi @BILearner,
The main idea is replacing the characters with the blanks at first, replacing the blanks with characters at last.
1. In the Query Editor, right click at the target value, select "replace values".
2. The measure would be like this:
One disadvantage: the format of the result has to be "Text".
Best Regards!
Dale
Hi @v-jiascu-msft,
I really appreciate the help. However, I want the columns data type to be numeric at the end because I am performing calculations on those columns. Also I do not want to replace characters such as '-' or N/A . I want them both as it is in the column and want those to be treated as Numeric
I may have data as following (This is the summarry level report) -
Year Revenue Quality
2017 Not reported 90%
2017 $250,000 95%
2016 $100,000 N/A
2015 $200,000 85%
2014 $250,000 -
The N/A and '-' has a different business value and are desired in the output. And at the same time I want these columns to be treated as numeric because I have to perform calculation(To generate new columns)/aggretaions on these columns(like avg, min max). In short I want the way to Treat these columns as numeric despite it contains character data.
I tried it with blanks and it works, but in this case I need to find a way to replace blanks with '-' and N/A at appropriate cells.
The second approach I thought I could use is create a measure with ISBLANK and replace blanks with N/A or '-', but the problem is ISBLANK would replace all the blanks with N/A or '-' How do I replace Blanks with N/A or '-' at appropriate row?
Any help would be appreciated,
Thanks!
Hi @BILearner,
Maybe we could add a new column, which will have 0 instead of "n/a" and "-". Then we could do some math operations in this new column. Finally, we could use the old and new columns to output a proper result.
Result = VAR sumq = SUM ( Table1[QuantityInNum] ) RETURN IF ( sumq = 0 && MIN ( 'Table1'[Quantity] ) = "n/a", "n/a", IF ( sumq = 0 && MIN ( 'Table1'[Quantity] ) = "-", "-", sumq ) )
Notes: 1. All the data of 2016 is "N/A" and all the data of 2014 is "-", right?
2. The type of result measure has to be "Text" because of the characters.
Best Regards
Dale
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |