let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source1= Table.SelectRows(Source, each Text.Start([Reporter ISO],1) <> "A"),
Source2 = Table.SelectColumns(Source1,{"Partner ISO","Trade Flow","Trade Value (US$)"}),
GroupBy1 = Table.Group(Source2, {"Partner ISO", "Trade Flow"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Total", each
List.Sum([#"Trade Value (US$)"]), type number}}),
AddTradeFlow = Table.AddColumn(GroupBy1, "TradeFlow2", each [Trade Flow] & "C"),
PivotTable = Table.Pivot(AddTradeFlow,{"Import","Export","Re-Import","Re-Export"},"Trade Flow","Total"),
PivotTable2 = Table.Pivot(PivotTable,{"ImportC","ExportC","Re-ImportC","Re-ExportC"},"TradeFlow2","Count"),
GroupBy2 = Table.Group(PivotTable2, {"Partner ISO"}, {{"SumImport", each List.Sum([Import]), type nullable number}, {"SumExport", each
List.Sum([Export]), type nullable number}, {"SumReImport", each List.Sum([#"Re-Import"]), type nullable number}, {"SumReExport", each
List.Sum([#"Re-Export"]), type nullable number}, {"CountImport", each List.Sum([ImportC]), type nullable number}, {"CountExport", each
List.Sum([ExportC]), type nullable number}, {"CountReImport", each List.Sum([#"Re-ImportC"]), type nullable number}, {"CountReExport",
each List.Sum([#"Re-ExportC"]), type nullable number}}),
ReplaceNull = Table.ReplaceValue(GroupBy2,null,0,Replacer.ReplaceValue,{"SumImport", "SumExport", "SumReImport", "SumReExport",
"CountImport", "CountExport", "CountReImport", "CountReExport"}),
ExtraColumn1 = Table.AddColumn(ReplaceNull, "CountTradeFlow", each "Export: " & Text.From([CountExport]) & "; Import: " &
Text.From([CountImport]) & "; Re-Export: " & Text.From([CountReExport]) & "; Re-Import: " & Text.From([CountReImport])),
ExtraColumn2 = Table.AddColumn(ExtraColumn1, "Total US$", each "Export: " & Text.From([SumExport]) & "; Import: " &
Text.From([SumImport]) & "; Re-Export: " & Text.From([SumReExport]) & "; Re-Import: " & Text.From([SumReImport])),
SelectColumn = Table.SelectColumns(ExtraColumn2,{"Partner ISO","CountTradeFlow","Total US$"}),
AddIndex = Table.AddIndexColumn(SelectColumn, "Index", 1, 1, Int64.Type),
ReOrder = Table.ReorderColumns(AddIndex,{"Index", "Partner ISO", "CountTradeFlow", "Total US$"})
in
ReOrder
