Rolling Data Report from two tables

[STIBBS_807]

I am looking for a way to cross reference two tables.  The first table has a list of reporting dates. The second is a list of transactions. I would like to cross reference the report table to the transactions table that show what vendor is open each month.  The Transactions has the Opening Date and may or may not have a Closing Date.

Example:

Report Dates:

     April 1, 2020 to March 31, 2021

Transaction:

     Vendor ID 0001 Open Date April 28, 2020 no closing date

     Vendor ID 0002 Open Date June, 2020  closing date Sep 21, 2020

My table would be:

Vendor ID 0001 April 2020 

Vendor ID 0001 May 2020 

Vendor ID 0001 Jun 2020 

Vendor ID 0002 Jun 2020 

Vendor ID 0001 Jul 2020 

Vendor ID 0002 Jul 2020 

Vendor ID 0001 Aug 2020 

Vendor ID 0002 Aug 2020 

Vendor ID 0001 Sep 2020 

Vendor ID 0002 Sep 2020

Vendor ID 0001 Oct 2020

Vendor ID 0001 Nov 2020

Vendor ID 0001 Dec 2020

Vendor ID 0001 Jan 2021

Vendor ID 0001 Feb 2021

Vendor ID 0001 Mar 2021

I am looking for how to do create this month by month table with Power Query in Power BI

[Jakinta · Accepted solution]

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

 

[Jakinta · Accepted solution]

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...

[STIBBS_807 · Accepted solution]

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?

[STIBBS_807 · Accepted solution]

Thank you Everyone for your help.

[STIBBS_807 · Accepted solution]

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?

[Jakinta · Accepted solution]

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

 

[STIBBS_807]

Can the parameter be programmed?  For example I have a statice start date.  My end date is always the end of the previous month from the current date.  

Also I am new to Power BI.  Is there any reference material for doing source coding like you have presented?

[Jakinta · Accepted solution]

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...

[STIBBS_807]

Thank You.