Text Grouping Using Group by feature in Power BI

Hi there, This Blog is about a scenario where you want to group the text data in one line. Let’s get started to understand it better.

Scenario: Below is the Table with 2 columns – StoreName and StoreType.

Observation: One StoreName has Multiple StoreType. There are 4 Distinct Values of StoreType – Recreational, Medical, Hybrid, Any/Other.

Objective: One StoreName should have one StoreType only.

Step 1: Click on the Group by Button present in the transform ribbon. Apply a sum aggregation on StoreType with StoreName as a basic selection. Let your new column name be – Text in One Line. With this, we will be able to make out what is the Unique Store Type status of distinct stores.

Step 2: Click OK and You will observe an error

Step 3: Click on Advance editor under Home Ribbon in your Query Editor.

By default, you will observe the following M Code:

As you can see in the last row inside the let code block List. Sum is mentioned. We need to change it to Text. Combine and Add a Separator of ‘-’ a hyphen for StoreType. Then, Click OK

You will observe the error will be removed and Your values in the Text in One Line Column will appear like below:

Multiple StoreType will be displayed next to distinct StoreName with a separator which will allow you to apply conditions as per your choice. Example

Any/Other-Hybrid = Hybrid

Any/Other-Medical = Medical

Any/Other-Recreational = Recreational

Hybrid-Medical = Hybrid

Hybrid-Recreational = Recreational

This will bring you close to your target objective.

Step 4: Add a Conditional column or you can also create your logical conditions in Custom Column. Write your logical conditions and you will have one Distinct Value in FinalStoreType Column.

Hit Close & Apply and Save your Changes. Here we have achieved our required objective.

Thank you for your valuable reading!

Happy Learning!

Raksha Gangwal

Data Analyst

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.