bjulius

let
    Source = Table.AddIndexColumn( FirstLadiesRaw, "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"Index", "First Ladies"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "First Ladies", "Cleaned"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Cleaned", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Cleaned.1", "Cleaned.2", "Cleaned.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","Rodham","",Replacer.ReplaceText,{"Cleaned.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Todd","",Replacer.ReplaceText,{"Cleaned.2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Bird","",Replacer.ReplaceText,{"Cleaned.2"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Welch","",Replacer.ReplaceText,{"Cleaned.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value3",{"Cleaned.1", "Cleaned.2", "Cleaned.3"},Combiner.CombineTextByDelimiter(" ",

bjulius

let
    Source = Table.Group(GreekRaw, {"Greek"}, {{"All", each _, type table [Seq=nullable text, Greek=nullable text]}}),
    CountRows = Table.RemoveColumns( Table.AddColumn(Source, "RowCount", each Table.AddIndexColumn( [All], "RowCount", 1, 1)), "All"),
    ExpandRowCounter = Table.ExpandTableColumn(CountRows, "RowCount", {"Seq", "RowCount"}, {"Seq", "RowCounter"}),
    SortIndex = Table.AddIndexColumn(ExpandRowCounter, "SortIndex", 1, 1, Int64.Type),
    GlobalMax = List.Max( Table.Group(SortIndex, {"Greek"}, {{"Count", each Table.RowCount(_), Int64.Type}})[Count] ),
    DistList = List.Distinct(SortIndex[Greek]),
    RepeatList =  Table.FromList(List.Repeat(DistList, GlobalMax), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Group = Table.Group(RepeatList, {"Column1"}, {{"All", each _, type table [Column1=text]}}),
    RptIdx = Table.RemoveColumns( Table.AddColumn(Group, "RepeatIdx", each Table.AddIndexColumn( [All], "RptIdx", 1,1)), "All"),

bjulius

let
    Source = Within20Raw,
    #"Sorted Rows" = Table.Sort(Source,{{"Champion", Order.Ascending}, {"Year", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index.1", 1, 1, Int64.Type),
    #"Added Index" = Table.AddIndexColumn(#"Added Index1", "Index", 0, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "Index0"}, {"Index.1", "Index1"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Champion", "Index0"}, #"Renamed Columns", {"Champion", "Index1"}, "Renamed Columns", JoinKind.LeftOuter),
    #"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"Year", "Champion"}, {"Year.1", "Champion.1"}),
    #"Filled Up" = Table.FillUp(#"Expanded Renamed Columns",{"Champion.1"}),
    Valid = Table.SelectRows( #"Filled Up", each [Champion] = [Champion.1]),

bjulius

let
    Source = RepeatsRaw,
    Unpivot = Table.TransformColumnTypes( Table.RenameColumns( Table.RemoveColumns( Table.UnpivotOtherColumns(Source, {"Year"}, "Attribute", "Value"), "Attribute"), {"Value", "Team"}), {"Year", Int64.Type}),
    Sort = Table.Sort(Unpivot,{{"Team", Order.Ascending}, {"Year", Order.Ascending}}),
    Group = Table.Group(Sort, {"Team"}, {{"All", each _, type table [Year=nullable number, Team=text]}}),
    AddIdx0 = Table.RemoveColumns( Table.AddColumn(Group, "Idx0", each Table.AddIndexColumn([All], "Index0", 0, 1)), "All"),
    Expand0 = Table.ExpandTableColumn(AddIdx0, "Idx0", {"Year", "Index0"}, {"Year", "Index0"}),
    AddIdx1 = Expand0,
    Expand1 = Table.RenameColumns( Table.TransformColumns(AddIdx1, {{"Index0", each _ + 1, type number}}), {{"Index0", "Index1"},{"Year", "YearPrev"},{"Team", "Team1"}}),
    Join = Table.Join( Expand0, {"Team", "Index0"}, Expand1, {"Team1", "Index1"}, JoinKind.LeftOuter),

bjulius

let
    Source = ScorersRaw,
    SplitByCharTrans = Table.SplitColumn(Source, "Highest Goal Scorer", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Highest Goal Scorer.1", "Highest Goal Scorer.2", "Highest Goal Scorer.3"}),
    SplitByDelim = Table.SplitColumn(SplitByCharTrans, "Highest Goal Scorer.2", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Highest Goal Scorer.2.1", "Highest Goal Scorer.2.2"}),
    SplitByCharTrans2 = Table.SplitColumn(SplitByDelim, "Highest Goal Scorer.2.1", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Highest Goal Scorer.2.1.1", "Highest Goal Scorer.2.1.2"}),
    RemoveDiacritics = Table.AddColumn(SplitByCharTrans2, "NoDiacritic", each  Text.FromBinary( Text.ToBinary( [Highest Goal Scorer.2.1.2], 1251), TextEncoding.Ascii)),
    RemoveCols = Table.RemoveColumns(RemoveDiacritics,{"Highest Goal Scorer.2.1.2", "Highest Goal Scorer.3"}),
    Reorder = Table.ReorderColumns(RemoveCols,{"Highest Goal Scorer.1", "

bjulius

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZPBTttAEIZfZcQplXpwQgjOEQIESKAoLlQV4jCxp/aSzQ5aey0lL8Az9FaOPfTUHtte3LxXZ0NiOZws+f88s/v/v+/v99r9/eDIpmQKZXDolNZk5wxRUb1MlSZohe/2Ht6vue5gSXHGueYSZwo/6GT1VcUZXNMjJbr6A62Dmg2PLS6VHlP1y6gEc2gdbrWD4FU7SmiuLLT6tdA9dyZFu4iqF5OwhRHHuZJP2+0aCc8smpguXV7AGZsClZEztve3QK/zOnyI1ioTZwit7lvtDsvqZef9IJOrjokNafDL44yWO8DmYGe6+mmVAHCkp2SLHSbiUlEBt0axuUO9dhQuSjRcvuFihVr5C1BCFjVM6MlNtYqBv8Bnl4rDWCo8sbj6iwYuyc5k9L/n5pTeDdvCpahPXV59nypu+HgYfCKZPpQg0SzkkcBV9dsHK04FNdW9YY0mGdolpWyXMMaCGzEdhnUtrtDKghHNn0jS6G2JsHNRoF7cIGuGCee5aoq9U5Ou54tvMJaYZn5/rfeD9ccR6lL2WoIolhA0xs0h/e6x02K8wnMrhvlasjRu5h1tQBMnu6WPlELkjZ9xUw4HlrEQP7GUTq2+ueZBOkGwacWEDeqE68KL0ttYeKXsOhMYac6prnknaAcb4mPGcyl5bXODuKYiI+udyCUWTQuIDKnH5A0WPUmV5YgqgTvvw454a13qcHGiJCmpvdW+gg2gO5AI5lOFlziXiCac2OpH6nyJ63u2w00g5/JrLGCE/sfx6sN/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Highest Goal Scorer" = _t]),
    SplitByCharTr

bjulius

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}}),

bjulius

let
    Source = Table.TransformColumnTypes( Table.DuplicateColumn( FatherSonRaw, "Name", "Surname"), {"Year", Int64.Type}),
    LastName = Table.RemoveColumns( Table.SplitColumn(Source, "Surname", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Surname.1", "LastName"}), "Surname.1"),
    Group = Table.Group(LastName, {"LastName", "Country"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Name=nullable text, Country=nullable text, Year=nullable number, LastName=nullable text]}, {"MinYear", each List.Min([Year]), type nullable number}}),
    Filter = Table.RemoveColumns( Table.SelectRows(Group, each ([Count] <> 1)), {"LastName", "Country", "Count"}),
    Expand = Table.ExpandTableColumn(Filter, "All", {"Country", "LastName", "Name", "Year"}, {"Country", "LastName", "Name", "Year"}),
    Relation = Table.AddColumn(Expand, "Relation", each if [Year] = [MinYear] then "Father" else "Son"),
    AgeDiff = Table.SelectRows( Table.AddColumn(Relation, "AgeDiff", each [Yea

bjulius

let
    Source = StandingsRaw,
    Group = Table.Group(Source, {"Conference"}, {{"All", each _, type table [Team=nullable text, Wins=nullable number]}}),
    RankCompetition = Table.SelectColumns( Table.AddColumn(Group, "NestRankComp", each Table.AddRankColumn( [All], "RankComp", { "Wins", Order.Descending}, [RankKind = RankKind.Competition ])), {"NestRankComp"}),
    RankDense = Table.RemoveColumns( Table.AddColumn(RankCompetition, "RankCompDense", each Table.AddRankColumn( [NestRankComp], "RankDense", { "Wins", Order.Descending}, [RankKind = RankKind.Dense ])), "NestRankComp"),
    RankOrdinal = Table.RemoveColumns( Table.AddColumn(RankDense, "RankCompDenseOrd", each Table.AddRankColumn( [RankCompDense], "RankOrdinal", { "Wins", Order.Descending}, [RankKind = RankKind.Ordinal])), "RankCompDense"),
    Expand = Table.TransformColumnTypes( Table.ExpandTableColumn(RankOrdinal, "RankCompDenseOrd", {"Team", "Wins", "Conference", "Date", "RankComp", "RankDense", "RankOrdinal"}, {"Team", "Wins", "Conference", "Dat

bjulius

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    SplitToRows = Table.ExpandListColumn(Table.TransformColumns(Index, {{"Data", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"),
    Group = Table.Group(SplitToRows, {"Index"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Data=nullable text, Index=number]}}),
    IncrementStep = Table.AddColumn(Group, "AddIncrement", each Table.AddIndexColumn([All],"Increment", 1,1)),
    Remove = Table.RemoveColumns(IncrementStep,{"Index", "All", "Count"}),
    Median = Table.AddColumn(Remove, "Median", each List.Median([AddIncrement][Increment])),
    Expand = Table.ExpandTableColumn(Median, "AddIncrement", {"Data", "Increment", "Index"}, {"AddIncrement.Data", "AddIncrement.Increment", "AddIncrement.Index"}),
    Filter = Table.SelectRows(Expand, each [Median