Forum Discussion
Need to Create a Separate Table of Calculated Columns
- 6 years ago
Yes, see this:
Becomes this in a few steps:
Use the M code below to see what I've done:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Donelet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MDAAUqZg0giIffPzSjJyKkFi+ob6RgZGBkqxOtFgKSMDHZhqKMMEiCNTE4vA6i3g6mMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [VendorID = _t, AmountOwed = _t, #"Payment Amount" = _t, #"Number of Payments" = _t, #"Payment Period" = _t, #"Payment Start Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"VendorID", Int64.Type}, {"AmountOwed", Int64.Type}, {"Payment Amount", Int64.Type}, {"Number of Payments", Int64.Type}, {"Payment Period", type text}, {"Payment Start Date", type date}}), #"Added Payment Count List" = Table.AddColumn(#"Changed Type", "Payment Count List", each {0..[Number of Payments]-1}), #"Expanded Payment Count List" = Table.ExpandListColumn(#"Added Payment Count List", "Payment Count List"), #"Added Payment Date" = Table.AddColumn(#"Expanded Payment Count List", "Payment Date", each if [Payment Period] = "Monthly" then Date.AddMonths([Payment Start Date], [Payment Count List]) else if [Payment Period] = "Yearly" then Date.AddYears([Payment Start Date], [Payment Count List]) else null, type date), #"Removed Other Columns" = Table.SelectColumns(#"Added Payment Date",{"VendorID", "Payment Amount", "Payment Date"}) in #"Removed Other Columns"I basically just created a list that goes from 0 to (# of payments -1), then incremented the months or years by that count using this formula, which is in the M code above, but this may be easier to read:
if [Payment Period] = "Monthly" then Date.AddMonths([Payment Start Date], [Payment Count List]) else if [Payment Period] = "Yearly" then Date.AddYears([Payment Start Date], [Payment Count List]) else null
If all you want to do is to calculate a cummulative/running total over time you don't need to materialize the data like that at all. It would be faster and simpler to just create a measure using a pattern like the following https://www.daxpatterns.com/cumulative-total/