Grouping Data

[maj]

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

[v-kelly-msft]

Hi @maj ,

Maybe you can simply use "groups" to realize it.See below:

Here is the reference:

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[maj]

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]

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!

[CheenuSing]

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]

Hi @CheenuSing,

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

[CheenuSing]

@maj ,

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

Cheers

CheenuSing

[maj]

@CheenuSing 

I'm not familiar with grouping.  Do you have any suggestion on how to write this in SQL?

[CheenuSing]

Hi @maj ,

Please send me your current query and also share a set of 10000 records in excel . Upload the excel and query to OneDrive or Google Drive and share the link . Please alsso mention what is the result you expect on the sample data.

Cheers

CheenuSing

[maj]

@CheenuSing

Thanks again for your help.  Here is the Google Drive link.

https://drive.google.com/file/d/14jonlT-1K_ByAZ6sQbVrMFen3spTWN3l/view?usp=sharing

Thanks!

-maj