Urgent -Start and end date issues

[Anonymous]

Hi everyone,

   I tried to fetch start and end date in single column by using functions in power query and added to invoked custom function while my start date and end date is same at that time it shows error from this power query 

(StartDate as date, EndDate as date) as table =>
let
Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
Length = Duration.Days(EndDate - StartDate)
in
#"Changed Type"

I attach my screenshot for your reference....kindly help me on this...

Advance thanks to all...

[mahoneypat · Accepted solution]

Just +1 to your length variable.  That also makes it so you see the end data in your list too.

(StartDate as date, EndDate as date) as table =>
let
Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
Length = Duration.Days(EndDate - StartDate)+1
in
#"Changed Type"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could add IF() formula to avoid errors. If StartDate is the same as EndDate, it can return StartDate.

Length = 
if EndDate = StartDate then Duration.Days(EndDate +#duration(1, 0, 0, 0) - StartDate) 
else Duration.Days(EndDate - StartDate)

[mahoneypat · Accepted solution]

Just +1 to your length variable.  That also makes it so you see the end data in your list too.

(StartDate as date, EndDate as date) as table =>
let
Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
Length = Duration.Days(EndDate - StartDate)+1
in
#"Changed Type"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@Anonymous , are you getting this error , While expanding the table with the icon at end of column name?

[Anonymous]

hi @amitchandak 

   Yes I am getting the error if the start date and end date is same at that row only shows error I already attached that with screenshot. Kindly help me on that

Thanks for your reply....