Excel BI Power Query Challenge #50 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Retype = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Group = Table.Group(Retype, {"Store"}, {"All", each [Date]}),
    AddDateLisit = Table.AddColumn(Group, "DateList", each [
        a = List.Min( [All] ),
        b = List.Max ( [All] ),
        c = Number.From( b ) - Number.From( a ) + 1,
        d = List.Dates( a, c, #duration(1,0,0,0))
    ][d]),
    Expand = Table.RemoveColumns( Table.ExpandListColumn(AddDateLisit, "DateList"), "All"),
    Right = Table.RenameColumns(Retype, {"Store", "Store1"}),
    Join = Table.RemoveColumns( Table.Join( Expand, {"Store", "DateList"}, Right, {"Store1", "Date"}, JoinKind.LeftOuter ), {"Date", "Store1"}),
    #"Sorted Rows" = Table.Sort(Join,{{"Store", Order.Ascending}, {"DateList", Order.Ascending}}),
    FillDown = Table.RenameColumns( Table.FillDown(#"Sorted Rows",{"Customer"}), {"DateList", "Date"}),
    ReGroup = Table.Group(FillDown, {"Store", "Customer"}, {{"All", each _, type table [Store=text, Date=date, Customer=text]}}),
    AddSeq = Table.RemoveColumns( Table.AddColumn(ReGroup, "AddSeq", each Table.AddIndexColumn( [All], "Seq", 1, 1)), "All"),
    ReExpand = Table.ExpandTableColumn(AddSeq, "AddSeq", {"Date", "Seq"}, {"Date", "Seq"}),
    Reorder = Table.ReorderColumns(ReExpand,{"Store", "Date", "Customer", "Seq"}),
    Trim = Table.TransformColumns(Reorder,{{"Store", Text.Trim, type text}})
in
    Trim