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 commented Apr 7, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.