Excel BI Power Query Challenge103 - Brian Julius Solution

gistfile1.txt

let
    Source = Table.DemoteHeaders( Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Transpose = Table.FillDown( Table.TransformColumns( Table.Transpose(Source), { "Column1", each if Text.StartsWith(_, "G") then _ else null}), {"Column1"}),
    UnpivotOther = Table.UnpivotOtherColumns(Transpose, {"Column1"}, "Attribute", "Value"),
    AddDept = Table.SelectRows( Table.SelectColumns( Table.AddColumn(UnpivotOther, "Dept", each if not List.Contains({"1".."9"}, Text.At(Text.From( [Value]), 0)) then [Value] else null), {"Column1", "Dept"}), each [Dept] <> null),
    AddTeam = Table.TransformColumnTypes( Table.SelectRows( Table.SelectColumns( Table.AddColumn(UnpivotOther, "Team", each if List.Contains({"1".."9"}, Text.At(Text.From( [Value]), 0)) then [Value] else null), {"Column1", "Team"}), each [Team] <> null ), {"Team", Text.Type}),
    Table = Table.FromColumns({
        Table.First( Table.FromColumns( List.Zip({Table.ToColumns(AddDept), Table.ToColumns(AddTeam)})))[Column1],
        Table.First( Table.FromColumns( List.Zip({Table.ToColumns(AddDept), Table.ToColumns(AddTeam)})))[Column2],
        Table.Last( Table.FromColumns( List.Zip({Table.ToColumns(AddDept), Table.ToColumns(AddTeam)})))[Column2]
    },{"Group", "Dept", "Team"}),
    Split = Table.TransformColumnTypes( Table.ExpandListColumn(Table.TransformColumns(Table, {{"Team", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Team"), {"Team", Int64.Type})
in
    Split