Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply

Group data by certain values in a column

I have a table of data. One of the columns contains a number + letter combination to identify the item.  Like 1A, 1B, 1C, 2A, 2B, 2C, M1A, C1A, etc.  I would like to create a grouping of the rows that match a certian name, and then reference this grouping in other calculation.  So say "Group 1" is 1A, 1B, 2A, 2B. How would I go about this?

 

Thanks

3 REPLIES 3
mytnikk
New Member

Hi, 

 

You can create conditional forum that will transform source IDs into Groups. 

mytnikk_0-1702470280014.png

 

 

Sample conditions: 

If ID column equals "1A" then "Group 1"  

If ID column equals "1B" then "Group 1"  

If ID column equals "2A" then "Group 1"  

If ID column equals "2B" then "Group 1"  

 

After that you will be able to GroupBy on New Group column. Hope it helps. 

 

 

 

DataVizzer
New Member

Hi @MichaelRensing,

to group rows by column values in Power BI Desktop, you can use the Group By feature in Power Query Editor. Here are the steps to do it:

  • Select your table of data and click Edit Queries on the Home tab.
  • In the Power Query Editor, select the column that contains the number + letter combination (e.g. Item).
  • Click Group By on the Transform tab.
  • In the Group By window, enter a name for your new column (e.g. Group) and select an aggregate function (e.g. Count Rows).
  • Click Advanced and add a condition for each group you want to create (e.g. Group 1 = Item = 1A or Item = 1B or Item = 2A or Item = 2B).
  • Click OK to apply the grouping.

You should see a new column with your groups and their counts. You can use this column to reference your groups in other calculations. 

 

Does this help? If so please mark this answer as accepted solution.

@DataVizzer  It makes sense, but I cannot figure out the second to last item. There doesn't seem to be a way to specify Item = 1A

 

Screenshot 2023-03-11 102513.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors