Power Query Grouping undos sort

I have a table listing providers (Table "Cities") and the cities they serve. If a provider is in multiple cities, they would be listed each time but on a separate row.

2nd table (Table "Providers") isolates and displays only unduplicated providers.

I am attempting to produce a table of unduplicated providers with 1 merged column of all the cities they serve. Cities should be separated by a delimiter (comma) and displayed alphabetically.

Steps followed in Power Query:
"Cities" Table
1. Sort by Provider Name and then City name.
2. Group rows
3. Added Custom column to isolate distinct rows by delimiter.
4. Extract values of list
At this point the Cities are displayed correctly with delimiter and in alphabetic order.

As soon as I merge "Provider" query to "Cities" query, the contents of the "Cities" column are no longer in alphabetic order. I'm not sure why this is happening.......frustrated!

Thanks Glenn. Can this be down inside power query?

do you want something like this?

Provider Name	Cities
A.W. HOLDINGS, LLC
ABC Healthcare of America, LLC	Atlanta, Columbus, Decatur, Snellville, West Snellville
Abagails Personal Care Home Agency Inc.	Locust Grover
Building Bridges Therapy
Comfort Care	Columbus
GCSS	Blairsville, Ellenwood, Hinesville
HIHOPE SERVICE CENTER INC
JIMMYS MAGNOLIA TREE INC
Williams Personal Care Home	Decatur, Griffin, Snellville, Stone Mountain

Yes Sandy!

so use this

// Providers
let
    Source = Excel.CurrentWorkbook(){[Name="Providers"]}[Content]
in
    Source

// Cities
let
    Source = Excel.CurrentWorkbook(){[Name="Cities"]}[Content]
in
    Source

// Join
let
    Join = Table.NestedJoin(Providers, {"Provider Name"}, Cities, {"Provider Name"}, "Cities", JoinKind.LeftOuter),
    Exp = Table.ExpandTableColumn(Join, "Cities", {"City"}, {"City"}),
    Grp = Table.Group(Exp, {"Provider Name"}, {{"Count", each _, type table}}),
    SortA = Table.Sort(Grp,{{"Provider Name", Order.Ascending}}),
    List = Table.AddColumn(SortA, "Cities", each List.Sort(List.Distinct([Count][City]), Order.Ascending)),
    Ext = Table.TransformColumns(List, {"Cities", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Ext

btw. Power Query is NOT vba so this is wrong subforum

that worked!

Originally Posted by KerahJoy

that worked!

I know

mark the thread as SOLVED (top above your first post - Thread Tools)