Forum Discussion
Excel "Sequence" function in Power Bi
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.
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.
17 Replies
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.
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
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.
You are wecome. Yes.
I'm sure it can be done but i think that would make the M code more complicated and lengthy.
