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
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
Custom1More 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… |