PowerQuery possible BUG filtering by a parameter?

Question

Hi, I am getting data from a SQL Server connection through PowerQuery's Transform Data Tool. I have two distinct tables (Table1, Table2), both of them have a column with a date (Date1, Date2) in text format '20230101','20211231'... (it seems like a number but it's formatted as text).

I need to filter both table's registers by date. I want to do it dynamically, by changing a date that is a Range in Excel, so I can update the date and refresh the data; then the data will be filtered by the new date (DateFilt).

To obtain this, I made a parameter inside Powerquery:

• Put the date "DateFilt" in a worksheet Range.
• Convert it to a table of 1 register and connect it to PowerQuery.
• Inside PQ, right click the table and click "Drill Down"

Now I have a parameter inside Powerquery to filter my tables with.

The issue is that I filtered Table1 by:

• Convert date column Date1 from text to whole number
• Filter greater or equal than DateFilt
• Convert date column Date1 again to text

And it works fine. But when I try to do same thing on the second table Table2, PowerQuery seems that keeps loading data but it doesn't refresh anytime! In the down-right corner usually appears how many registers PW has checked/is checking but this time nothing appears. It seems like it is freezed.

Is something wrong with my method of filtering a column by a value located in a cell? I can attach the codes/scripts but both look the same. Both tables have the same register quantity more or less too. I am desperated.

Thanks a lot in advance!

Answer 1

Hi, I am getting data from a SQL Server connection through PowerQuery's Transform Data Tool. I have two distinct tables (Table1, Table2), both of them have a column with a date in text format (Date1, Date2).

That's the point where the problem begins and all you've done after that makes no sense.

You said you want to filter by a date, so let us do some basics how that works with normal dates.

.

If we load that into PQ and filter by date > 11-Nov we can do that very simple and the last step of the MCode is this:

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] > #date(2023, 11, 11))

All we have to do to make that dynamic is to replace #date(2023, 11, 11) with our parameter. So if we create a parameter "MyDate" of data type Date with a default value we can change the MCode to

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] > MyDate)

Done.

That means the first step you have to perform is to convert your text in Date1, Date2 into real dates. After that you can filter the data as I've shown pretty easy.

You can also read the parameter from a table in Excel, have look here and MCode below.

Building a Parameter Table for Power QueryThe Excelguru Blog

Andreas.

// fnGetParameter
let
Source = let func = (TableName as text, ParameterName as text, optional ParameterColumn as text, optional ValueColumn as text) =>
let
Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
// Lower case all headings to ignore case
ParamTable = Table.RenameColumns(Source,List.Transform(Table.ColumnNames(Source),each {_,Text.Lower(_)})),
ParamCols = Table.ColumnNames(ParamTable),
ParamCol = if ParameterColumn = null then ParamCols{0} else Text.Lower(ParameterColumn),
ValueCol = if ValueColumn = null then ParamCols{1} else Text.Lower(ValueColumn),
ParamRow = Table.SelectRows(ParamTable, each Record.Field(_, ParamCol) = ParameterName),
Value = if Table.IsEmpty(ParamRow)=true then null else Record.Field(ParamRow{0},ValueCol)
in
Value,
documentation = [
Documentation.Name = "fnGetParameter",
Documentation.Description = "Returns a parameter from a table",
Documentation.LongDescription = Documentation.Description,
Documentation.Category = "Parameter",
Documentation.Source = "******@gmx.net",
Documentation.Version = "1.2",
Documentation.Author = "Andreas Killer",
Documentation.Examples = {[
Description = "
FullName = fnGetParameter(""Setup"",""Path"") & fnGetParameter(""Setup"", ""Filename""),
",
Code = "
TableName : Name of the table in Excel that contains the parameters
ParameterName : Text to be found in the ParameterColumn
ParameterColumn: Name of the column containing the ParameterName (1st column if omitted)
ValueColumn : Name of the column containing the value to be returned (2nd column if omitted)

You'll get the “Information is required about data privacy” prompt, see:
https://blog.crossjoin.co.uk/2017/05/24/data-privacy-settings-in-power-bipower-query-part-1-performance-implications/

", Result = ""]}]  

in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
in
Source