Using Parameters to select a column

mitchandsuzy
Posts: 20
Joined: Tue Nov 17, 2015 4:00 pm

Using Parameters to select a column

Postby mitchandsuzy » Tue Nov 07, 2017 12:03 pm

Okay, I thought this would be so simple, but no at least not for me!

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!) :lol:

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.

Code: Select all

let
    fxRemoveDates = (Source as table , mColName as text) =>
let
    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))
in
    #"Filtered Rows1"
in
    fxRemoveDates



If anyone knows how to have a parameter interpreted as a column I would be very grateful.

Suzy

MattAllington
Posts: 999
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Using Parameters to select a column

Postby MattAllington » Tue Nov 21, 2017 6:25 pm

Hi Suzy

Do you have a sample you can post instead of the code? I haven't looked, but is there an end of month function in Power Query to find the last day of the current month eg EOM(Today()) [not real code] to work it out?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest