Excel BI Challenge 76 - Brian Julius Solution

gistfile1.txt

let

  Source = NewcomersRaw,
  ReplaceAnd = Table.ReplaceValue(Source,"and",",",Replacer.ReplaceText,{"The Newcomers"}),
  Split = Table.SplitColumn(ReplaceAnd, "The Newcomers", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)),
  Unpivot = Table.UnpivotOtherColumns(Split, {"Year"}, "Attribute", "Value"),
  Trim = Table.TransformColumns(Unpivot,{{"Attribute", Text.Trim, type text}, {"Value", Text.Trim, type text}}),
  Filter = Table.SelectRows(Trim, each ([Value] <> "" and [Value] <> "s" and [Value] <> "Tobago" and [Value] <> "Herzogovenia" and [Value] <> "the Grenadines" and [Value] <> "Principe" and [Value] <> "Nevis")),
  FirstLetter = Table.SplitColumn(Filter, "Value", Splitter.SplitTextByPositions({0, 1}, false), {"Alphabet", "Value.2"}),
  Group = Table.Group(FirstLetter, {"Alphabet"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
  Rank = Table.AddRankColumn( Group, "Rank", {"Count", Order.Descending}, [RankKind = RankKind.Dense] ),
  FilterNClean = Table.RemoveColumns( Table.SelectRows(Rank, each [Rank] <= 3), "Rank")
in
  FilterNClean

Looks simple, but absolutely wicked curveball in this one - filter step needs to include all countries with " and " in the name to avoid counting as two countries:

Filter = Table.SelectRows(Trim, each ([Value] <> "" and [Value] <> "s" and [Value] <> "Tobago" and [Value] <> "Herzogovenia" and [Value] <> "the Grenadines" and [Value] <> "Principe" and [Value] <> "Nevis"))