Groups

[Macpanzer]

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

[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]

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.