cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
STIBBS_807
Frequent Visitor

Rolling Data Report from two tables

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

4 ACCEPTED SOLUTIONS
Jakinta
Solution Sage
Solution Sage

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_0-1619702019763.png Jakinta_1-1619702401040.png

 

 

 

View solution in original post

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

View solution in original post

STIBBS_807
Frequent Visitor

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:

STIBBS_807_0-1620747837396.png

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?

View solution in original post

STIBBS_807
Frequent Visitor

Thank you Everyone for your help.

View solution in original post

6 REPLIES 6
STIBBS_807
Frequent Visitor

Thank you Everyone for your help.

View solution in original post

STIBBS_807
Frequent Visitor

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:

STIBBS_807_0-1620747837396.png

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?

View solution in original post

Jakinta
Solution Sage
Solution Sage

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_0-1619702019763.png Jakinta_1-1619702401040.png

 

 

 

View solution in original post

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?

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

View solution in original post

Thank You.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors