Forum Discussion

kerwin73's avatar
kerwin73
New Member
3 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    3 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
        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