Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |