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