Rolling Data Report from two tables

Hi,

Try this. Please note that Parameters are used for Report Start/End. 

This probably should be converted to function, but...

Just Replace Source with your table/previous step and Column1 with your column name.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvNS8kvUvB0UTAwMDBU8C9IzVNwSSxJVXAsKMrMUTCy0FEwMjAyUMjLV0jOyS/OzEtXSAFKK8XqoOk1QtLrVZqXCtF3aAGKNoXg1AIFI0OInFJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), {"Column1"}),
    Start = Table.AddColumn(Source, "Start", each if Text.Contains([Column1],"no closing date")
then Text.Combine(List.Range(Text.Split([Column1]," "), 5, 3)," ") else Text.Combine(List.Range(Text.Split([Column1]," "), 5, 2)," ")),
    End = Table.AddColumn(Start, "End", each if Text.Contains([Column1],"no closing date")
then ReportEnd else Text.Combine(List.Range(Text.Split([Column1]," "), 9, 3)," ")),
    Dates = Table.TransformColumnTypes(End,{{"Start", type date}, {"End", type date}}),
    StartDate = Table.AddColumn(Dates, "DateStart", each List.Max({[Start],ReportStart})),
    EndDate = Table.AddColumn(StartDate, "DateEnd", each List.Min({[End],ReportEnd})),
    #"Removed Columns" = Table.RemoveColumns(EndDate,{"Start", "End"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"DateStart", type date}, {"DateEnd", type date}}),
    MonthsRange = Table.AddColumn(#"Changed Type", "MonthsRange", each (12 * (Date.Year([DateEnd]) - Date.Year([DateStart])))
+ (Date.Month([DateEnd]) - Date.Month([DateStart]))
+ 1),
    List = Table.AddColumn(MonthsRange, "MonthsList", each List.Numbers(1, [MonthsRange])),
    Expanded = Table.ExpandListColumn(List, "MonthsList"),
    Months = Table.AddColumn(Expanded, "OpenMonths", each Text.Combine(
  {Text.Start(Date.MonthName(
  Date.AddMonths(
   [DateEnd],0 - [MonthsRange] + [MonthsList])),3),  Number.ToText(Date.Year(Date.AddMonths([DateEnd],0 - [MonthsRange] + [MonthsList])))}, " ")),
    Removed = Table.SelectColumns(Months,{"Column1", "OpenMonths"}),
    Vendor = Table.AddColumn(Removed, "Vendor", each Text.Combine(List.Range(Text.Split([Column1]," "), 0, 3)," ")),
    Removed2 = Table.SelectColumns(Vendor,{"Vendor", "OpenMonths"}),
    #"Grouped Rows" = Table.Group(Removed2, {"OpenMonths"}, {{"GR", each _, type table [Vendor=text, OpenMonths=text]}}),
    #"Expanded GR" = Table.ExpandTableColumn(#"Grouped Rows", "GR", {"Vendor"}, {"Vendor"}),
    FINAL = Table.CombineColumns(#"Expanded GR",{"Vendor", "OpenMonths"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Result")
in
    FINAL

I hope you have created parameters as suggested previously.

If you did, , right click on ReportEnd parameter, AdvancedEditor and completely replace existing code with:

let
    Source = Date.From(Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), -1))
in
    Source

In ReportStart parameter enter whichever date for desired static date, as initial value, and it should work...

Thank you for your answer.  As a result I have learn much about viewing the Source using the advance editor.

I am new so I am going to as this simple question.

The  source statement I am going to use an excel spread sheet. I have 16 columns of data that I am using in my month by month data set.  These are my columns:

I should not have to worry to much about the splitting of the table list items as they already are defined.  Am I correct on that assumption?

Thank you Everyone for your help.