Forum Discussion

royces99's avatar
royces99
Frequent Visitor
3 years ago
Solved

Add columns based off today's date and previous 3 months

I'm trying to add columns to a table with the column names being today's date and the previous 3 months (last day of month).  The code below is free of syntax errors but the last row of code shown generates the error message "Expression.Error: 2 arguments were passed to a function which expects between 3 and 4.  Details: Pattern= Arguments=[List]" and I don't know how to resolve it - what are the other 1 or 2 arguments that I need?  Can anybody help?

 

// STEP 1: Read the initial data
Source = database,

// Get today's date
today = DateTime.Date(DateTime.LocalNow()),

// Calculate the list of date column names
numColumns = 3,
column_names = List.Generate(
() => [i = 0, prevMonthEnd = today],
each [i] < numColumns,
each [i = [i] + 1, prevMonthEnd = Date.From(Date.EndOfMonth(Date.From(Date.AddMonths([prevMonthEnd], -1))))],
each if [i] = 1 then Text.From([prevMonthEnd], "dd-MM-yyyy") else Text.From([prevMonthEnd], "dd-MM-yyyy")
),

// Create a list of columns to add
columnsToAdd = List.Transform(column_names, each [Name = _ & " Column", Type = Int64.Type]),

// Add the columns
databaseWithColumns = Table.AddColumn(Source, columnsToAdd),

  • royces99 replace last 2 steps with the following

    databaseWithColumns = 
       List.Accumulate(
          column_names, 
          Source, 
          (s, c) => Table.AddColumn(s, c & " Column", each null, Int64.Type)
       )

2 Replies

  • royces99 replace last 2 steps with the following

    databaseWithColumns = 
       List.Accumulate(
          column_names, 
          Source, 
          (s, c) => Table.AddColumn(s, c & " Column", each null, Int64.Type)
       )
  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion

    databaseWithColumns = Table.SelectColumns(Source,Table.ColumnNames(Source)&List.Transform({1..numColumns},each Date.ToText(Date.EndOfMonth(Date.AddMonths(today,_-1)),"dd-MM-yyyy")),2)