Forum Discussion

maj's avatar
maj
Helper I
6 years ago

Grouping Data

Hello,

 

I have a very large dataset (26MM rows) that contains hundreds of customer names.  I want to group them as such:

 

If customer name is A, group A

If customer name is B, group B

If customer name is C, group C

All customers (including A, B, C), group All Customers

 

Is this possible?  Thanks!

-maj

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi maj ,

     

    This is doable in PowerQuery. Considering the umber of records it is best to do this at the source than in Power BI.

     

    What is your data source and type of load - Import or Direct.

     

    Cheers

     

    CheenuSing

    • maj's avatar
      maj
      Helper I
      6 years ago

      Hi Anonymous,

       

      My data source is a dataflow that was created through a connection to an Azure Databricks table.  I created the table in Azure using SQL query.  Are you saying it's best to do the grouping in my query, which feeds the dataflow, which feeds my PBI report?

       

      Thanks!

      -maj

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        maj ,

         

        Yes  it's best to do the grouping in  query, which feeds the dataflow, which feeds my PBI report.

         

        Cheers

         

        CheenuSing

    • maj's avatar
      maj
      Helper I
      6 years ago

      Hi v-kelly-msft,

       

      I don't think grouping will work for this scenario.  I created a group for customer A, another group for customer B, and another group for customer C.  Now I want to create a group for all customers, including customers A, B, and C.  However, the customers A, B, and C are no longer available in the ungrouped values section because they are already grouped.  The 'Include Other group' only uses the remaining ungrouped values, and doesn't give me an option to use both the ungrouped and grouped values.  Please let me know if there is a way around this.  Thanks!

       

      -maj

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi maj ,

         

        How about using a measure or calculated column to do the grouping?

        Measure as below:

         

        Measure = 
IF(CONTAINSSTRING(SELECTEDVALUE('Table'[Customer]),{"A,B,C"})=TRUE(),"A,B,C",IF(SELECTEDVALUE('Table'[Customer]) IN {"A"},"A",IF(SELECTEDVALUE('Table'[Customer]) IN {"B"},"B",IF(SELECTEDVALUE('Table'[Customer]) IN {"C"},"C",BLANK()))))

         

        And you will see:

        Or you can create a calculated column if you need to put it in a column or another certain visual:

         

        Column = 
IF(CONTAINSSTRING('Table'[Customer],{"A,B,C"})=TRUE(),"A,B,C",IF('Table'[Customer] IN {"A"},"A",IF('Table'[Customer] IN {"B"},"B",IF('Table'[Customer] IN {"C"},"C",BLANK()))))

         

        And you will see:

         

        For the related .pbix file, pls click here.

         

        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!