Normalize related pairs of Origins and Destinations

[Vienna83]

Hi to all,

I'm struggling with a - to my mind - simple task, which was in a way solved in this Community Message: http://community.powerbi.com/t5/Desktop/Transform-Data-with-Power-Query/td-p/62740 

I have a table with following columns:

Origin | Destination | Passengers

Vienna | Paris | 1200

Vienna | Berlin | 900

Vienna | Moscow | 400

Vienna | London | 4500

Berlin | Vienna | 1100

Paris | Vienna | 1800

Moscow | Vienna | 1600

I'm trying to create a table in PowerBI which can be used for a FlowMap, so I need to kind of normalize the pairs and sum the Passenger values:

Origin | Destination | Passengers

Vienna | Paris | 3000

Vienna | Berlin | 2000

Vienna | Moscow | 2000

Vienna | London | 4500

How can I realize that with a PowerQuery?

Cheers and thank you!

[MarcelBeug · Accepted solution]

My suggestion would be to add a column with Origin and Destination sorted alphabetically in nested records, remove the original columns, expand the record column, group by the 2 new columns and sum the Passengers.

let
    Source = OriginsAndDestinations,
    #"Added Custom" = Table.AddColumn(Source, "Sorted", each if [Origin] > [Destination] then [Origin = [Origin], Destination = [Destination]] else [Origin = [Destination], Destination = [Origin]], type [Origin = text, Destination = text]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Origin", "Destination"}),
    #"Expanded Sorted" = Table.ExpandRecordColumn(#"Removed Columns", "Sorted", {"Origin", "Destination"}, {"Origin", "Destination"}),
    #"Grouped Rows" = Table.Group(#"Expanded Sorted", {"Origin", "Destination"}, {{"Passengers", each List.Sum([Passengers]), type number}})
in
    #"Grouped Rows"

[Vienna83]

@MarcelBeug That worked out perfectly! I tried it (now at home) with a piece of example data:

My sample was neutralized to that table:

Thank you!