Pivot Table but Transpose values

MasonF2265 

And here is with Power Query

- query the table

- group by Id without aggregation and keeping only lists of cities

- calculate max number of locations and generate list of column names

- extract lists as values and split after that on columns

Script is

let
    Source = Excel.CurrentWorkbook(){[Name="Cities"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID number"}, {{"Location", each _[City]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Count", each List.Count([Location])),
    allLocations = {1..List.Max(#"Added Custom"[Count])},
    Columns = List.Transform(allLocations, each "Location " & Text.From(_)),
    #"Extracted Values" = Table.TransformColumns(
        #"Added Custom",
        {"Location", each Text.Combine(List.Transform(_, Text.From), ";"), type text}
    ),
    #"Split Column by Delimiter" = Table.SplitColumn(
        #"Extracted Values",
        "Location", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), Columns),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Count"})
in
    #"Removed Columns"