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  Ther...
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.

     

     

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.

      • Valnus's avatar
        Valnus
        Helper II
        4 years ago

        Hi 

         

        I will give this a try. 

        Do I assume correctly that i need to run through the data set and define the colums for each diffrent string we want to extract and group ?