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
Author By
Kamal Sharma
Kamal brings over 20 years of experience in data analytics and business intelligence. He has led the design and implementation of analytics solutions across operations, financial reporting, and performance improvement initiatives. With a background in business statistics and Six Sigma, his work focuses on applying data in a structured and practical way to solve real business challenges.
