Forum Discussion
kerwin73
3 years agoNew Member
Basic syntax question - using List.Dates function
Hi, There are many responses to similar questions on this board, but unfortunately I am completely lost on how to implement. I am attempting to create a new column that I can expand. I believe I ca...
- 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
kerwin73
3 years agoNew Member
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)
)
),
#"Expanded DateList" = Table.ExpandListColumn(Custom1, "DateList")
in
#"Expanded DateList"