Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
let
fn = (Start as any, End as any, Step as number, Unit as text, SplitDateAndTime as text) as table =>
let
TypeStart = GetTypeAsText(Start),
TypeEnde = GetTypeAsText(End),
GetTypeAsText = (Value as any) as text =>
let
nonNullableType = Type.NonNullable(Value.Type(Value)),
FindType =
if Type.Is(nonNullableType, type date) then "date"
else if Type.Is(nonNullableType, type datetime) then "datetime"
else error "Unknown type"
in
FindType,
//check, if both parameter have the same type
Output =
if TypeStart <> TypeEnde then
Table.FromList({"Inputdata have different data types!"})
//check if type is date or datetime
else if GetTypeAsText(Start) = "Unknown type" then
Table.FromList({"Data type must be date or datetime!"})
//check, if Start > End
else if Start > End then
Table.FromList({"End parameter has to be bigger than Start parameter!"})
//Check if "Step" is positive
else if Step < 0 then
Table.FromList({"Step has to be a positive number!"})
else
try
Table.TransformColumnTypes(
Table.FromList(
List.Generate(
()=>Number.From(Start),
each _ <= Number.From(End),
each _ +
(
if Unit = "Day" then
Step
else if Unit = "Hour" then
Step/24
else if Unit = "Minute" then
Step/24/60
else Step/24/60/60
)
),
Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//{{"Column1", Value.Type(Start)}}
{{"Column1", if Unit = "Day" then type date else type datetime}}
)
otherwise Table.FromList({"Error"}),
OutputSplit =
if SplitDateAndTime = "yes" then
if Unit <> "Day" then
//Split Date and time
Table.RemoveColumns(
Table.AddColumn(
Table.AddColumn(Output, "Date", each DateTime.Date([Column1]), type date),
"Time", each DateTime.Time([Column1]), type time
),{"Column1"}
)
else
Output
else
Output
in
OutputSplit,
fnType = type function(
Start as any,
Ende as any,
Step as number,
Unit as (type text meta [Documentation.AllowedValues =
{"Day", "Hour", "Minute", "Second"}]),
SplitDateAndTime as (type text meta [Documentation.AllowedValues={"yes", "no"}])
) as list meta [
Documentation.Name = "GetTableOfDateAndDateTime",
Documentation.LongDescription =
"Easily create a column of date or datetime values,
with any positive increment on day-, hour-, minute
or second-level.",
Documentation.Author = "Lars Schreiber, www.ssbi-blog.de"
]
in
Value.ReplaceType(fn, fnType)
@SchreiberLars

This comment has been minimized.

Copy link
Owner Author

@SchreiberLars SchreiberLars commented Apr 7, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment