Need to Create a Separate Table of Calculated Columns

[ccambron]

My question, in general, is how to loop through the rows of a table and generate, either a separate table or a table in a column with coumns that I calculate based on the values in the original table. This can be in DAX or in M. I have already shared my list and created calculated columns in M. I am open to eirther approach. Whichever is easier.

So, I have a table that lists Vendors like this:

VendorID	AmountOwed	Payment Amount	Number of Payments	Payment Period	Payment Start Date
1	1000	500	2	Monthly	5/1/2020
2	20,000	5,0000	4	Yearly	8/1/20

I need to create a new table of payments like this ( either stand alone or inside of a new column in the original table:

Vendor ID	Payment Amount	Payment Date
1	500	5/1/2020
1	500	6/1/2020
2	5,000	8/1/20
2	5,000	8/1/21
2	5,000	8/1/22
2	5,000	8/1/23
etc.

I can't find the exact scenario anythwhere on the forums or the web. I feel like it should be fairly easy. For each row, I would create a loop with the number of payments as the counter and add records as above until number of payments is done. I don't need help with figureing out ddates or anything, just with how to create a new list/table so that I can ultimately end up with a comprehensive list that I can show a bar graph with combined payments over time. 

[edhans · Accepted solution]

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 Done

let
    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

[ccambron]

Thanks so much for all of that code. I am much slower getting off the ground with M than I thought and I am up agaist a deadline.

I am going through it and it looks like exactly what I need.

On the first part, did you copy and paste the table I did and thats why it is Json? I have an existing table with all of these columns, instead of Table.FromRows, should I use something else to pull in my last query step as the table source?

 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]),

[edhans]

Yes, when you enter data in Power BI via the "Enter Data" box it puts it in an encrypted compressed JSON blob.

If you want to use the last step of Query A as the source for Query B, simply use this:

Source = #"Query A"

or 

Source = QueryA

 as the first line. The #"something" is how to reference something in PQ with spaces or special characters. 

[ccambron]

For instance, table.selectcolumns?

[ccambron]

I accepted it as a solution because I can clearly see that it does what I need. I am trying to adapt it. I simplified some of the field names when I submitted it and I need the rows to come from my last step. I am trying to alter it and am running into errors: #'AddPaymentAmount" is my last step on the database. I know something needs to change after the first comma and before "let".
Also, "Amount Owed" is actually "Cost Deferment", Payment Period is actually Payment Schedule and Payments Start Date is actually Deferred Payments Start Date.

let
Source = #"Add Payment Amount", let _t = ((type text) meta [Serialized.Text = true]) in type table [VendorID = _t, Cost Deferment = _t, #"Payment Amount" = _t, #"Number of Payments" = _t, #"Payment Schedule" = _t, #"Payment Start Date" = _t]),
#"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 Schedule] = "Monthly" then Date.AddMonths([#"Deferred Payments #(lf)Start Date"], [Payment Count List])
else if [Payment Schedule] = "Yearly" then Date.AddYears([#"Deferred Payments #(lf)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"

[edhans]

Your Source Line in that code will not work. 

The easiest way to reference another query is to select that query, right-click, and choose REFERENCE. It will create the correct source line for you.

And you might be ok, but columns like #"Deferred Payments #(lf)Start Date" generally give me pause as there is a line feed, which you shouldn't do as a rule.

[ccambron]

The reference I get when I right-click th elast query is the same as what I have: #"Add Payment Amount"

[ccambron]

Success!

I have my new table. Thanks so much!

This is the final code:

let
Source = #"Add Payment Amount",
#"Filtered Rows" = Table.SelectRows(Source, each ([No of Months] > 0 )),
#"Added Payment Count List" = Table.AddColumn(#"Filtered Rows", "Payment Count List", each {0..[No of Months]-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 Schedule] = "Monthly" then Date.AddMonths([#"Deferred Payments #(lf)Start Date"], [Payment Count List])
else if [Payment Schedule] = "Yearly" then Date.AddYears([#"Deferred Payments #(lf)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"

[edhans]

Excellent @ccambron - glad my code helped you arrive at your solution. When I was referring to your source line being incorrect, it was this:

Source = #"Add Payment Amount", let _t = ((type text) meta [Serialized.Text = true]) in type table [VendorID = _t, Cost Deferment = _t, #"Payment Amount" = _t, #"Number of Payments" = _t

But that might have just been some copy/paste issues in this forum.

Hope the logic of what I did helps and if you have any questions, let me know!

[ccambron]

That field name gives me pause also, but that is the name I get when I drill down and it seems to work. Using the field name as it appears doesn't work. I'm sure there is a reason, maybe a stray character in the title, but I have't had time to investigateit and it seems to work - even though t is ugly.

[ccambron]

Do I mark your subsequent answer as soutions or just the first solution? I think I am really close.

I have altered the code - see below. I think it is close, but I am getting an error saying that the field "Number of Months" of the record wasn't found.  When I scroll down in the expression error, I can see it. It says Num of Months = 0. That column is there. I added a filter to take out rows with no "No of Months", but it's not working. 

********************

let
Source = #"Add Payment Amount",
#"Filtered Rows" = Table.SelectRows(Source, each ([No of Months] > 0 )),
#"Added Payment Count List" = Table.AddColumn(Source, "Payment Count List", each {0..[Number of Months]-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 Schedule] = "Monthly" then Date.AddMonths([#"Deferred Payments #(lf)Start Date"], [Payment Count List])
else if [Payment Schedule] = "Yearly" then Date.AddYears([#"Deferred Payments #(lf)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"

[d_gosbell]

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/