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