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: 932
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".
https://exceleratorbi.com.au/power-bi-online-training/


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests