Select value from table as input to PowerQuery DateKey function

[wi11iamr]

Hi there,

I have a Date Function which creates a DateKey, and this function takes a StartDate and EndDate as parameter.

I've traditionally used the function with a List.Min and List.Max function to find my Start and End dates, as follows:

= fnDateDim(Date.From(List.Min(AssetRegister[dDatecreated])),
                      Date.From(List.Max(AssetRegister[dDatecreated]))
                     )

Looking for the Min and Max values has however become unnecessarily laborious as the entire table is loaded before the Min and Max dates are identified, and for large tables, this is a waste.

I've since created a summarised dateRange table with Start and End dates for various attributes Date Types:

DateType                 StartDate        EndDate

InventoryDate	2016/07/06	2017/09/02
InstallationDate	1970/01/01	2020/07/21
ReportDate	2017/02/28	2017/09/05
AuditDate	2017/09/05	2017/09/11

The trouble I'm having is in trying to use the respective StartDate & EndDate in my DateKey function, as depending on how I use the Table.SelectRows and Table.SelectColumns functions, I get various errors.

The below code:

= fnDateDim(
	Table.Column(
		Table.SelectRows(dimDateRanges,			
			each [Datetype] = "InstallationDate"), 
		"StartDate"),
Date.From("2017/06/30"), 
"en-us")

Gives the error:

"cannot convert a value of type List to type Date"

and then when I try to wrap it in Date.From function:

= fnDateDim(Date.From(
	Table.Column(
		Table.SelectRows(dimDateRanges,			
			each [Datetype] = "InstallationDate"), 
		"StartDate")),
Date.From("2017/06/30"), 
"en-us")

I get the error:

"couldn't convert to Date."

Assuming that my Table.SelectRows and Table.Column functions have managed to correctly return the single value of the InstallationDate's StartDate that I am looking for, then I suspect my issue lies in not understanding the format in which a value from a Column is returned.

Any pointers welcome!

[v-jiascu-msft · Accepted solution]

Hi @wi11iamr,

How about replacing Date.From with List.Min? As you said, there is only one single value returned.

= fnDateDim(List.Min(
	Table.Column(
		Table.SelectRows(dimDateRanges,			
			each [Datetype] = "InstallationDate"), 
		"StartDate")),
Date.From("2017/06/30"), 
"en-us")

Table.Column returns a list. Reference: https://msdn.microsoft.com/en-us/library/mt260736.aspx.

Date.From can't take a list as parameter. Reference: https://msdn.microsoft.com/en-us/library/mt260687.aspx

Best Regards!

Dale

[wi11iamr]

Fantastic, thanks very mcuh @v-jiascu-msft!!