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