Excel BI Power Query Challenge 63 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReType = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}}),
    RemoveBlankRows = Table.SelectRows(ReType, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    ReplaceNull = Table.ReplaceValue(RemoveBlankRows,null,"Date",Replacer.ReplaceValue,{"Column2"}),
    UnpivotOther = Table.UnpivotOtherColumns(ReplaceNull, {"Column1"}, "Attribute", "Value"),
    AddNumbers = Table.AddColumn(UnpivotOther, "Numbers", each if List.ContainsAny( Text.ToList( Text.From( [Value] )), {"0".."9"}) then [Value] else null),
    AddLabels = Table.FillDown( Table.AddColumn(AddNumbers, "Labels", each if List.Contains( {"Samples", "Date", "Prodict", "Plan", "Actual", "Duration"}, [Value]) then [Value] else null), {"Labels"}),
    AddSite = Table.FillDown( Table.AddColumn(AddLabels, "Site", each try if Text.StartsWith( [Column1], "Plant" ) then [Column1] else null otherwise null), {"Site"}),
    Filter = Table.SelectRows(AddSite, each ([Numbers] <> null)),
    RemoveCols = Table.RemoveColumns(Filter,{"Attribute", "Value"}),
    AddDate = Table.AddColumn(RemoveCols, "Date", each if [Labels] = "Date" then [Numbers] else null),
    Add0Index = Table.AddIndexColumn(AddDate, "Index", 0, 1),
    AddMod = Table.AddColumn(Add0Index, "Mod", each Number.Mod( [Index], List.Count( List.RemoveNulls( List.Distinct( AddDate[Date] ))))),
    DateModTable = Table.Distinct( Table.SelectRows( Table.PrefixColumns( Table.SelectColumns( AddMod, {"Date", "Mod"}), "x"), each [x.Date] <> null )),
    Join = Table.Join( AddMod, "Mod", DateModTable, "x.Mod", JoinKind.LeftOuter),
    FillDownCol1 = Table.FillDown(Join,{"Column1"}),
    RemCols2 = Table.SelectRows( Table.RenameColumns( Table.RemoveColumns(FillDownCol1,{"Date", "Index", "Mod", "x.Mod"}), {"x.Date", "Date"}), each [Labels]<>"Date"),
    PlantTable = Table.SelectRows( RemCols2, each Text.StartsWith( [Column1], "Plant")),
    PivotPlantTable = Table.PrefixColumns( Table.Pivot(PlantTable, List.Distinct(PlantTable[Labels]), "Labels", "Numbers"), "z"),
    ProductTable = Table.SelectRows( RemCols2, each Text.StartsWith( [Column1], "Product")),
    PivotProductTable = Table.Pivot(ProductTable, List.Distinct(ProductTable[Labels]), "Labels", "Numbers"),
    Join2 = Table.Join(PivotProductTable, {"Site", "Date"}, PivotPlantTable, {"z.Site", "z.Date"}),
    Clean = Table.RenameColumns( Table.SelectColumns( Join2, {"Site", "Date", "z.Duration", "z.Samples", "Column1", "Plan", "Actual"}), {{"z.Duration", "Duration"}, {"Column1", "Product"}}),
    AddDateSort = Table.AddColumn(Clean, "DateSort", each Date.FromText( [Date])),
    Sort = Table.RemoveColumns( Table.Sort(AddDateSort,{{"DateSort", Order.Ascending}, {"Site", Order.Ascending}}), "DateSort")
in
    Sort