Groups

[Macpanzer]

Possibility of "search" and "alphabetic order" in Groups funtcions

[CPCARDOSO]

Currently, the Power BI Groups/Bins creation window does not offer a search box or alphabetic ordering of values; this makes the feature unsuitable for very large lists, such as 20,000 municipalities. For this volume, instead of keeping municipalities manually grouped in the "Groups" feature, the most robust approach is to create a mapping table Municipality–Country (in Excel, SQL, or Power Query) and relate it to the model. This way, you have search, sorting, and centralized maintenance. Whenever you need to correct a municipality, just change a single line in the mapping table, without depending on the Groups window. In addition, the same mapping can be reused in other reports.

If this helps you, please consider marking this post as the accepted solution in the forum and, if possible, take a moment to help another user by answering a question there as well.

Look this exemples:
Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn
Best practice rules to improve your model’s performance | Blog do Microsoft Power BI | Microso...
https://community.powerbi.com/t5/Desktop/Best-practices-for-PBI/td-p/1476026

[v-priyankata]

Hi @Macpanzer 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@Nasif_Azam @raisurrahman Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @Macpanzer 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[Macpanzer]

Thank you!

[Nasif_Azam · Accepted solution]

Hey  @Macpanzer ,

In Power BI, it is true that managing large datasets with a list of municipalities can be cumbersome without a proper search or alphabetic ordering function in the grouping options. Here are a few approaches to improve your workflow, along with the steps to implement each solution:

1) Preprocessing in Power Query with Sorting & Grouping: To organize your municipalities alphabetically before grouping, use Power Query to preprocess your data. Steps:

• Open Power BI Desktop.

• Click on Transform Data to open Power Query Editor.

• Select the column with the municipalities (e.g., Municipality Name).

• To sort the municipalities alphabetically: Right-click the column header and choose Sort Ascending.

• To group municipalities into countries (if they are not already):

  • Select the Municipality Name column and go to the Transform tab.

  • Choose Group By.

  • In the dialog box, select Advanced and choose to group by Country or any relevant field.

2) Using DAX for Custom Grouping:

If you prefer to manage your groups using DAX, you can create a calculated column to assign each municipality to its respective country. Steps:

• In Power BI Desktop, go to Modeling and select New Column.

• Use a DAX formula like the following to create the groups:

CountryGroup = SWITCH(TRUE(),
  'Municipality'[Name] = "BERGAMO", "Italy",
  'Municipality'[Name] = "FERRARA", "Italy",
  'Municipality'[Name] = "LONDON", "UK",
  'Municipality'[Name] = "PARIS", "France",
  // Add more mappings here
  "Other"  // Default case
)

3) Creating a Lookup Table for Municipalities and Countries: If the data is large and involves many countries, you can create a lookup table that maps municipalities to countries. This can be done manually in Excel or another data source and then imported into Power BI. Steps:

• Create a table in Excel with two columns: Municipality Name and Country.

• In Power BI, go to Home > Get Data and select Excel.

• Import the municipality-country mapping table.

• Once imported, create a relationship between the municipality column in your main dataset and the Municipality Name column in the lookup table.

4) Using Excel for Additional Sorting and Grouping: If you need more advanced sorting and grouping tools:

• Open the dataset in Excel.

• Use the Sort function (Data > Sort) to arrange your municipalities alphabetically.

• Apply filters or use Excel’s Find & Replace to correct groupings quickly.

• Import the corrected dataset into Power BI by clicking Home > Get Data > Excel.

Best Regards,
Nasif Azam

[raisurrahman]

@Macpanzer  I’d appreciate it if you could describe the problem in detail. Is it in Power Query or Power BI?

[Macpanzer]

Sorry, Powerby. For various reasons and database limitations, I need to group approximately 20,000 different municipality names into their respective countries. During my check, however, I realized that several were grouped in the wrong group, but it's impossible to quickly search for the municipality to correct, as there is no tool available.

[raisurrahman]

It’s better to address this through data modeling. You can create a Country dimension along with City and Municipality.