How to use variable column name in a function for other functions?

Question

I have written two functions which take date as input and I'm gonna use them on multiple queries. Instead of doing manual work every time (call both functions, filter rows where first function returns True, expand record of the second function to columns, delete first function column) I thought I'd write another function that takes names of the table and the column with dates as parameters to automatize that process. My current table-based function works if I include specific column's date in the code, but those names will be different between different queries(tables).

Here's the table function's code:

(t as table) =>
let
    FunctionFilter = Table.AddColumn(t, "DateFilter", each DateFilter([myDate2])),
    FunctionPeriods = Table.AddColumn(#"FunctionFilter", "TimePeriods", each TimePeriods([myDate2])),
    ExpandPeriods= Table.ExpandRecordColumn(FunctionPeriods, "TimePeriods", {"Year", "Quarter", "Month", "WeekMon", "WeekTue", "Day"},
    {"Year", "Quarter", "Month", "WeekMon", "WeekTue", "Day"}),
    TrueDate = Table.SelectRows(ExpandPeriods, each ([DateFilter] = true)),
    DeleteDateFilter = Table.RemoveColumns(TrueDate,{"DateFilter"})
in
    DeleteDateFilter

My only problem is inserting a variable column name in place of [myDate2] here:

    FunctionFilter = Table.AddColumn(t, "DateFilter", each DateFilter([myDate2])),
    FunctionPeriods = Table.AddColumn(#"FunctionFilter", "TimePeriods", each TimePeriods([myDate2])),

Using Table.Column(t,[column name]) returns a list instead of a date, which causes called date functions to throw a type mismatch error.

Aleksei Zhigulin Aleksei Zhigulin · Accepted Answer · 2019-07-26T14:10:17

You may use such technique:

// Table
let
    Source = #table(3,List.Zip({{"a".."d"},{1..4},List.Numbers(10,4,10)})),
    fn = fn(Source, "Column3")
in
    fn

// fn
(tbl as table, col as text) =>
let
    i = Table.AddIndexColumn(tbl, "i", 0, 1),
    add = Table.AddColumn(i, "new",  each Table.Column(i, col){[i]}*10),
    del = Table.RemoveColumns(add, "i")
in
    del

How to use variable column name in a function for other functions?

1 Answers