cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mzrkwcz Regular Visitor
Regular Visitor

Get a list of dates from range and schedule

I am making a sales etimates report and I'm stuck at the problem of using the deal information to create the table of future invoices.

 

I have a table of Deals with following fields:

  • period_start = DATE
  • period_end = DATE
  • schedule = one of ("once", "monthly", "quarterly")

Now I need to create the table of invoices, with the date column:

  • if schedule is "once", it should be just one row containing period_start
  • if schedule is "monthly", I want to get one row with period_start date, and following rows with dates incremented by 1 month but not exceeding period_end date
  • if schedule is "quarterly", I want to get similar result like above, with 3 months increment.

I have no idea how to start... can you suggest something, please?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Get a list of dates from range and schedule

This should get you started:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then {[period_start]} else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"

 

It creates additional rows per month or quarter with their respective 1st days.

 

@Greg_Deckler: Do you know where to post/adress forum bugs like the missing signatures ?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




7 REPLIES 7
Super User
Super User

Re: Get a list of dates from range and schedule

I'm not sure this is the best scenario for Power BI. It sounds like you are trying to "invent" data using Power BI and that's probably not what you should be using Power BI for. You might be able to get there with something like 3 CALCULATETABLE expressions and then a UNION or two but dicey. There also is likely an M code solution to this where you would call functions that would return the data you need for your invented data. But, you probably should use Excel to generate the data and then just import it into Power BI.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


mzrkwcz Regular Visitor
Regular Visitor

Re: Get a list of dates from range and schedule

I was thinking of using M code (to create a column with the list of dates in Deals table)...

 

I do not think Excel is a solution here - I am using the data read directly from the REST API of SaaS CRM, Pipedrive. It is updated automatically and results distributed with shared dashboards are used by other team members

Super User
Super User

Re: Get a list of dates from range and schedule

In theory you could do this via M code with functions but that's a bit beyond me but @ImkeF could probably show you the way. If she can't, then it's not even possible.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Get a list of dates from range and schedule

This should get you started:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then {[period_start]} else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"

 

It creates additional rows per month or quarter with their respective 1st days.

 

@Greg_Deckler: Do you know where to post/adress forum bugs like the missing signatures ?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mzrkwcz Regular Visitor
Regular Visitor

Re: Get a list of dates from range and schedule

Wow, thanks ImkeF, it looks like you made me started, thanks.

 

I am fresh new at M, so I need to experiment with this for a while...

sam241 Visitor
Visitor

Re: Get a list of dates from range and schedule

How about this? (for anyone else who's still interested)

 

 

= Table.AddColumn( Source, "invoice_date", each

if schedule = "once" then period_start else

if schedule = "monthly" then List.Generate( () => [ a = [period_start], b = [period_end] ], each [a] <= [b], each [ a = Date.AddMonths( [a], 1 ), b = [b] ], each [a] ) else

if schedule = "quarterly" then List.Generate( () => [ a = [period_start], b = [period_end] ], each [a] <= [b], each [ a = Date.AddQuarters( [a], 1 ), b = [b] ], each [a] ) else

null

)

 

 

And then expand the lists, as normal.

 

In case it helps you, this is the reference document for the List.Generate function.

JohnThomas Frequent Visitor
Frequent Visitor

Re: Get a list of dates from range and schedule

Awsome!  Love this code.  We use it to generate a good data set from a workbook that has start date / end date for when we Hire and ROF (Reduction of Force) Craft employees.  This, given the shift they are working (5/10's for example, would be 5 days per week, 10 hours per day) allows us to calculate forecast cost and hours with straight time and overtime by day!  One simple PowerQuery step, turns a super complicated workbook into a simple 1 line per employee.  Additionally, we can now generate daily, weekly, monthly, quarterly charts and tables by employee, discipline, work area, ets.

 

I love PQ!