What I am trying to do is create a function that removes all the data (or Filters actually) based on what the current month is. If this is November the I do not want any results from November showing because it is only a partial figure and can distort the "big picture" (and the boss doesn't want it there!)
I have tried Table.Column(Source, mColName) but that creates a list of that column, I have tried "[" & mColName & "]" and Table.ColumnNames(mColName) neither work.
This is what it should be doing.... /* Table.SelectRows(#"Sorted Rows", each ([On] <= #date(2017, 10, 31) )) */
I can just add the code to the individual queries but that is not a very neat solution -
the source is the previous statement eg #"Sorted Rows" and the mcolname is the column name that has the dates to filter.
fxRemoveDates = (Source as table , mColName as text) =>
curDate = DateTime.LocalNow(),
curYear = Date.Year(curDate),
curMonth = Date.Month(curDate),
lastMonth = if curMonth = 1 then 12 else curMonth -1,
lastYear = if curMonth = 1 then curYear -1 else curYear,
varLastMonth = Text.From(lastYear) & "-" & Text.From(lastMonth) & "-01",
lastDayofprevMonth = Date.DaysInMonth(DateTime.FromText(varLastMonth)),
#"Filtered Rows1" = Table.SelectRows( Source, each (mColName) <= #date(lastYear, lastMonth, lastDayofprevMonth))
If anyone knows how to have a parameter interpreted as a column I would be very grateful.