Forum Discussion
Basic syntax question - using List.Dates function
- Anonymous3 years ago
Hi kerwin73 - The error message is attempting to explain that does not know where to find the columns used in your List.Dates function. For example, [pse_Start_Date__c] is a column in the Object called #"Extracted Date1". But Power Query does know this. To help, I need to understand how many rows are included in the Table.
If it is just one, then this might work.let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Extracted Date1" = Table.TransformColumns(Source,{{"pse__Start_Date__c", DateTime.Date, type date}, {"pse__End_Date__c", DateTime.Date, type date}}), Custom1 = List.Dates( List.Min( #"Extracted Date1"[pse__Start_Date__c]), Number.From ( List.Min( #"Extracted Date1"[pse__End_Date__c] ) - List.Min( #"Extracted Date1"[pse__Start_Date__c] ) + 1, #duration(1,0,0,0) ) in Custom1If there are multiple rows, then this would work.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Extracted Date1" = Table.TransformColumns(Source,{{"pse__Start_Date__c", DateTime.Date, type date}, {"pse__End_Date__c", DateTime.Date, type date}}), Custom1 = Table.AddColumn( #"Extracted Date1" , "DateList", each List.Dates( [pse__Start_Date__c]), Number.From ( [pse__End_Date__c] - [pse__Start_Date__c] ) + 1, #duration(1,0,0,0) ) ) in Custom1
Hi kerwin73 - The error message is attempting to explain that does not know where to find the columns used in your List.Dates function. For example, [pse_Start_Date__c] is a column in the Object called #"Extracted Date1". But Power Query does know this. To help, I need to understand how many rows are included in the Table.
If it is just one, then this might work.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Extracted Date1" = Table.TransformColumns(Source,{{"pse__Start_Date__c", DateTime.Date, type date}, {"pse__End_Date__c", DateTime.Date, type date}}),
Custom1 = List.Dates(
List.Min( #"Extracted Date1"[pse__Start_Date__c]),
Number.From (
List.Min( #"Extracted Date1"[pse__End_Date__c] ) -
List.Min( #"Extracted Date1"[pse__Start_Date__c] ) + 1,
#duration(1,0,0,0)
)
in
Custom1
If there are multiple rows, then this would work.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Extracted Date1" = Table.TransformColumns(Source,{{"pse__Start_Date__c", DateTime.Date, type date}, {"pse__End_Date__c", DateTime.Date, type date}}),
Custom1 = Table.AddColumn( #"Extracted Date1" , "DateList", each
List.Dates(
[pse__Start_Date__c]),
Number.From ( [pse__End_Date__c] - [pse__Start_Date__c] ) + 1,
#duration(1,0,0,0)
)
)
in
Custom1