Date Parameters

[DrDatastein]

Hello,

I am using a procedure to make all my reports have a lower time limit so that I only have 13 months of data. To do this, I have an Excel file that has date values that provide Today (=today()) and several other dates that include the use of the =today() formula.

The problem that is showing up is this: When I open the Excel file, I see the value for today and it is always today's date. When I bring the file into a Power Query, the today value might be 4 days behind. I always refresh the preview in the query window, but nothing seems to make this value current. I am concerned that the other date values may not respond as I would expect when Feb. 1 comes around.

Thoughts on why this might be occurring?

Tim

 

[Ken Puls]

Have you tried clicking the "Refresh Preview" button inside Power Query?

Since it's so slow, Power Query actually caches the preview data that it uses inside the editor so as not to drive you mad waiting for it to load. This has the unfortunate byproduct of having out of date data inside that window. (When you do a data --> refresh all in Excel, it will recalculate in Excel, but the preview may still be cached.)

 

[DrDatastein]

Hi Ken,

Thanks for the quick response. Yes, I think I almost wore out the "Refresh Preview" button It had no effect.

Tim

 

[Ken Puls]

My bad, you did say that.

No, that's odd. What is the code that you're using to pull this data into Power Query. Can you share the code you see in the Advanced Editor? I'm honestly trying to think of what one could do to cause this issue...

When you refresh your query via Data --> Refresh All, does it pull the right value, or no? Is there any chance at all that you have hard coded the date by accident when setting a filter on records?

 

[DrDatastein]

Hi Ken,

Great questions. Here's the structure:

1. I have an Excel file with date parameters that I use to confine the dates of reports. Each of these are based on the '=today()' formula.

View attachment 6323
For instance, [SOM13] is expressed as =EOMONTH(TODAY(),-14)+1


2. Here is the code to bring in the date parameter:
let
Source = Excel.Workbook(File.Contents("G:\_RESULTS HCA CENTRAL FILES\Parameters.xlsx"), null, true),
Parameters_Table = Source{[Item="Parameters",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Parameters_Table,{{"Today", type date}, {"SOQ5", type date}, {"SOQ1", type date}, {"EOQ", type date}, {"SOM13", type date}, {"SOM3", type date}, {"SOM1", type date}, {"EOM", type date}, {"Index", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"SOM13", "Index"})
in
#"Removed Other Columns"

3. I merge the [SOM13] variable into the larger dataset using the [Index] as the matching variable.

4. I use the 'Fill Down' procedure to populate [SOM13] value to all rows.

5. Then I create a variable that evaluates if the date in the larger dataset is before or after [SOM13]. Dates before are filtered out. This gives me a report that is always the most recent 13 months.

Tim

 

[DrDatastein]

Hi Ken,

Interestingly, I just tried the 'From Folder' query method, rather than the 'From File' method. This resulted in the [Today] variable expressing today's date. Hmmm.

It's a few more steps to create, but if it shows to be reliable then I'll use it.

Tim

 

[Ken Puls]

This, right here, is bad news:

File.Contents("G:\_RESULTS HCA CENTRAL FILES\Parameters.xlsx")

Power Query does not open the external workbooks and recalculate the formulas. It works with the most recent saved copy. So if you haven't opened the file in several days, it will read the old date.

Try creating a table in the current workbook, and attached to it using the command to get data from a table in the current workbook instead. That way you'll always be pulling from a recalculated version.

 

[DrDatastein]

Oh boy. OK, that's good to know. I was putting the parameters in a sheet inside the file; however, I noticed that I was using them in several files. I decided that putting the parameters file in a central location and hitting it from all the other queries might work out. I'll adjust a bit.

Thanks for your help.

Tim