Forum Discussion

Valnus's avatar
Valnus
Helper II
4 years ago
Solved

Data cleanup and Grouping

Hi All 

 

I have a very dirty data set ( text and numbers ). Below is the complaint field that is used as freetext in the system. As a result there is Numeric and Text values in the column 

There are many Strings that are the same. For example "Cough" . I would like to group all the instances of "cough" and there clinicid. 

I was tinkering with a Meassure using groupby but that does not work also I used a IF statment to create a new colum to inidicate if the text value is there. That works but given the scale of clean up this won't be viable. 

Is there another way to approach this ?




clinicid        complaint

2Feel sick
3SNEEZING AND COUGHING
4BP CHECK UP
5COUGH/TIGHT CHEST PAIN
6Cough
7Severe Backache / UTI
8BP Check up
9c/o Pain Chest / Stomach Pains.
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Valnus ,

     

    Yes, you need to run through the data set and defind the columns for all different strings you want to extract and group in you code.

    Just like this code. I create a calculated column to run through the dataset and defind the [complaint] column for all different strings I want and put them into groups like Cough/Check Up / Stomach and so on.

    Group = 
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Table'[complaint], "Cough" ), "Cough",
    CONTAINSSTRING ( 'Table'[complaint], "Check Up" ), "Check Up",
    CONTAINSSTRING ( 'Table'[complaint], "Stomach" ), "Stomach",
    CONTAINSSTRING ( 'Table'[complaint], "Backache" ), "Backache",
    "None"
)

     

    Best Regards,
    Rico Zhou

     

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

     

     

5 Replies

  • negi007's avatar
    negi007
    Community Champion
    4 years ago

    Valnus  you can use the group option in the powerbi. right click on the compliant column and you will get new group option, here you can create a new group by grouping items in different groups which are there in the data.

     

     

     

    l hope this option will work for you.

    • Valnus's avatar
      Valnus
      Helper II
      4 years ago

      Hi 

      It's an option. 

      The problem is that the data set is 680 000 records. Is there no way to search key words and add them to a new colum ? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Valnus ,

         

        Here I suggest you to create a calculated column in your table as the group.

        Group = 
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Table'[complaint], "Cough" ), "Cough",
    CONTAINSSTRING ( 'Table'[complaint], "Check Up" ), "Check Up",
    CONTAINSSTRING ( 'Table'[complaint], "Stomach" ), "Stomach",
    CONTAINSSTRING ( 'Table'[complaint], "Backache" ), "Backache",
    "None"
)

        Result is as below.

         

         

        Best Regards,
        Rico Zhou

         

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