Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BILearner
Advocate I
Advocate I

How to treat columns that has most numerical data and some characte data

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!

1 ACCEPTED 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 )
    )

How to treat columns that has most numerical data and some characte data .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Employee
Employee

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".How to treat columns that has most numerical data and some characte data.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. The measure would be like this:How to treat columns that has most numerical data and some characte data2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

One disadvantage: the format of the result has to be "Text".

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 )
    )

How to treat columns that has most numerical data and some characte data .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.