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

     

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
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

 

  • ccambron's avatar
    ccambron
    Frequent Visitor
    6 years ago

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

     

    • ccambron's avatar
      ccambron
      Frequent Visitor
      6 years ago

      For instance, table.selectcolumns?

      • ccambron's avatar
        ccambron
        Frequent Visitor
        6 years ago

        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's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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.