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 can use the List.Dates function to create the column and nested table. The nested table will have an incremented date between the start and end dates.

Can you please help, I believe my syntax is incorrect.

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

I believe this is related to 2nd argument in Custom1 ... however, unknown to me how to resolve.

Code:

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([pse__Start_Date__c], [pse__End_Date__c]-[pse__Start_Date__c]+1,#duration(1,0,0,0))
in
    Custom1

Need Help

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

4 Replies

kerwin73

New Member

3 years ago

Apologies, reading closer, even a basic syntax issue I could figure out.

Missing open parenthesis around start date field, then added back your final close paren on custom function.

Thanks Anonymous

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"

kerwin73

New Member

3 years ago

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"

Anonymous

Not applicable

3 years ago

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

kerwin73

New Member

3 years ago

Anonymous - thank you so much for giving this basic question your attention.

The step you suggest appears to have gotten me closer, but not all the way to desired end state.

It is likely that my description is/was incomplete (see additional context below).

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Full snippet, less your final ")" which was generating expected comma error.

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

More context:

Data set pre custom function:

Project Name	Task Name	BG_WBS_Number__c	Sum of pse__Estimated_Hours__c	pse__Start_Date__c	pse__End_Date__c
Project 1	Task 1	10.10.20 - Project Management	10	1/20/2023	1/26/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023
Project 1	Task 3	10.10.20 - Project Management	4	1/24/2023	1/24/2023

Desired data set post custom function:

Project Name	Task Name	BG_WBS_Number__c	Sum of pse__Estimated_Hours__c	pse__Start_Date__c	pse__End_Date__c	DateList
Project 1	Task 1	10.10.20 - Project Management	10	1/20/2023	1/26/2023	1/20/2023
Project 1	Task 1	10.10.20 - Project Management	10	1/20/2023	1/26/2023	1/21/2023
Project 1	Task 1	10.10.20 - Project Management	10	1/20/2023	1/26/2023	1/22/2023
Project 1	Task 1	10.10.20 - Project Management	10	1/20/2023	1/26/2023	1/23/2023
Project 1	Task 1	10.10.20 - Project Management	10	1/20/2023	1/26/2023	1/24/2023
Project 1	Task 1	10.10.20 - Project Management	10	1/20/2023	1/26/2023	1/25/2023
Project 1	Task 1	10.10.20 - Project Management	10	1/20/2023	1/26/2023	1/26/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023	2/1/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023	2/2/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023	2/3/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023	2/4/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023	2/5/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023	2/6/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023	2/7/2023
Project 1	Task 2	10.10.20 - Project Management	40	2/1/2023	3/28/2023	2/8/2023
						Etc…

Forum Discussion

Basic syntax question - using List.Dates function

4 Replies

Related content

List.Dates function- Count error

Question on the use of List.Dates function

Question about Power Query functions

Bookmark questions

Architecture question and brainstorming