create a new column that includes combined values of a grouping

[dw700d]

I have  a location code, each location code consists of multiple location types. I would like to create a column in the query editor that identifies all the possible location types attributable to  a location code and combines it into one row. For example location Code A has 4 location types ( Mall,Store,House and Vendor) so any row where A is the location code I would see (Mall, Store,House,Vendor) in the proposed column.  Order is not important to me

Location Types
LOCATION CODE	LOCATION_TYPE	Proposed Column
A	Mall	Mall,Store,House,Vendor
A	Store	Mall,Store,House,Vendor
A	House	Mall,Store,House,Vendor
A	Vendor	Mall,Store,House,Vendor
B	Mall	Mall,Hospital,School
B	Hospital	Mall,Hospital,School
B	School	Mall,Hospital,School
C	Mall	Mall
D	House	House,School
D	School	House,School
E	Vendor	Vendor,Mall,House
E	Vendor	Vendor,Mall,House
E	Mall	Vendor,Mall,House
E	House	Vendor,Mall,House
L	Mall	Mall,Store
L	Mall	Mall,Store
L	store	Mall,Store
L	store	Mall,Store
L	Mall	Mall,Store
L	Mall	Mall,Store
L	Mall	Mall,Store
P	store	Store,house
P	house	Store,house
P	house	Store,house
P	house	Store,house

[CNENFRNL]

[TomMartens · Accepted solution]

Hey @dw700d ,

if you want to use Power Query than you can use the following steps:
Starting with a table that looks like this:

Group the table using  Transform --> Group by:

The name of the new column is not that important, of course it will be used in subsequent steps.

Add a new custom column and use this formula:

//transforms the list values into a separated string by the given separator
Text.Combine(
    // removes duplicates from the list
    List.Distinct(
    // extracts the column values from the column LOCATION_TYPE     
    [GroupedValues][LOCATION_TYPE]
    ) , ","
)

The result should look like this:

Finally, you can expand the column GroupedValues.

Hopefully, this provides what you are looking for.

Regards,

Tom

[Jihwan_Kim]

Hi,

In case if you want to create a new column by using DAX formula, please check the below.

New Column CC =
CONCATENATEX (
    SUMMARIZE (
        FILTER (
            'Location Types',
            'Location Types'[LOCATION CODE] = EARLIER ( 'Location Types'[LOCATION CODE] )
        ),
        'Location Types'[LOCATION_TYPE]
    ),
    'Location Types'[LOCATION_TYPE],
    ", "
)