Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Excel "Sequence" function in Power Bi

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

 

SM4437_0-1653020153887.png

 

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

 

Any help would be appreciated.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

17 REPLIES 17
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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?

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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?

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

SM4437_0-1653272990775.png

 

This is the end result im looking for

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

SM4437_0-1653276176904.png

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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)

 

SM4437_0-1653340967291.png

 

Hi,

Download the PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

You are wecome.  Yes.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

thanks for your help

Vijay_A_Verma
Super User
Super User

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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.