Excel "Sequence" function in Power Bi

[Anonymous]

I'd like to be able to replicate this output in Power Bi

Any help would be appreciated, col T to Y are in a single table.

Any help would be appreciated.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUYpMTSwCUgamuoYGukYGhmZAjqGBUqwOirQhsrQpuqwRsqwRuqyBoa6BGUjWHMgxwStroRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Interval = _t, Period = _t, #"Interval Start" = _t, #"Repeat in contract term" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Interval", Int64.Type}, {"Period", type text}, {"Interval Start", type date}, {"Repeat in contract term", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Repeat in contract term", "Repeat in contract term - Copy"),
    #"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each {1..[Repeat in contract term]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Subtracted from Column" = Table.TransformColumns(#"Expanded Custom", {{"Custom", each _ - 1, type number}}),
    #"Inserted Multiplication" = Table.AddColumn(#"Subtracted from Column", "Multiplication", each [Interval] * [Custom], type number),
    #"Added Custom1" = Table.AddColumn(#"Inserted Multiplication", "Custom.1", each Date.AddYears([Interval Start],[Multiplication])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Repeat in contract term", "Multiplication"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Repeat in contract term - Copy", "Repeat in contract term"}}),
    #"Added Prefix" = Table.TransformColumns(#"Renamed Columns", {{"Custom", each "Date " & Text.From(_, "en-IN"), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Added Prefix","Date 0","Interval Start Date",Replacer.ReplaceText,{"Custom"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Replaced Value", {{"Custom", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Replaced Value", {{"Custom", type text}}, "en-IN")[Custom]), "Custom", "Custom.1")
in
    #"Pivoted Column"

Hope this helps.

[Anonymous]

Hi Ashish, is there a way to modify the script so that it calculates the due dates from a different frequency? The intervals are not only yrs but also months?

[Ashish_Mathur]

I'm sure it can be done but i think that would make the M code more complicated and lengthy.

[Anonymous]

Understood, appreciate your help in solving this for me. If we can try the extended M Code that would be great. Would it make it easier if the interval was mths only? we could change the year interval to months?

[Ashish_Mathur]

We can try.  Show a dataset where yu have different interval periods and also show the expected result very clearly.

[Anonymous]

This is the end result im looking for

[Ashish_Mathur]

That image does not appear properly when i click on it - the background is black and therefore cannot be seen.  Also, share your source data in a form that can be pasted in an MS Excel file.

[Anonymous]

This is the result i want, for some reason i get an error meassge when trying to paste in table?

[Ashish_Mathur]

The Repeat in contract terms column is missing.  Show that column as well (will that be in years or columns).  show the expected result clearly.

[Anonymous]

The repeat col is the number of col that is created, this value will change depending on the number of times the sequence is repeated in the contract term (eg a 10 year contract with a monthly interval will repeat 120 times, ie 120 columns/dates)

[Ashish_Mathur]

Hi,

Download the PBI file from here.

Hope this helps.

[Anonymous]

Thanks Ashish, immnot that familiar with M code, im assuming i simply change the source section?

[Ashish_Mathur]

You are wecome.  Yes.

[Anonymous]

thanks for your help

[Vijay_A_Verma]

Taking first row as example, why Due Date1 should be 4/10/2021 if they are spaced at 5 years interval? It should be 5/10/2021 and Due Date 2 should be 5/10/2026 not 3/10/2026. You should not multiply 365 by 5 to arrive at 1825 days. You need to simply add 5 years to dates to arrive at final result unless you have reasons to add 1825 days not 5 years. 

Let me know the logic, then I can provide appropriate DAX formula.

[Anonymous]

Hi, thanks for the quick response, simply multiplying years by 365 in excel was the simplest way to go, the end result isnt that sensitve. That said appreciate if you could optimise the calc in Dax.

The end result i need is the progressive due date based on the interval displayed in seperate columns

[Anonymous]

Also, the interval on each row can change between years, 3M, 6M, 1W