Forum Discussion

ccambron's avatar
ccambron
Frequent Visitor
6 years ago
Solved

Need to Create a Separate Table of Calculated Columns

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...
  • edhans's avatar
    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 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