Forum Discussion
Calculated Table - Calendar function with 15 minutes interval
Hi,
I have a data for every 15 min and there are 3 different source files I am receiving I want to create a common calculated table to show the same interval which will help to make a relationship table and I can able to perform the action.
can anyone guide me in this part?
Hi nagoor,
To cover dates for entier year, please try this:
Source = List.Dates(#date(2018, 1, 1),365, #duration(1, 1, 0, 0)),
Regards,
Yuliana Gu
4 Replies
Hi nagoor,
Please new a Blank Query, in Adviced editor, please enter below code:
let Source = List.Dates(#date(2018, 1, 1), 5, #duration(1, 1, 0, 0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "TimeColumn", each List.Times(#time(0, 0, 0), 96, #duration(0, 0, 15, 0))), #"Expanded TimeColumn1" = Table.ExpandListColumn(#"Added Custom", "TimeColumn"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded TimeColumn1", {{"Column1", type text}, {"TimeColumn", type text}}, "en-US"),{"Column1", "TimeColumn"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateTime") in #"Merged Columns"Best regards,
Yuliana Gu
Thanks v-yulgu-msft
Its worked, but only for 5 days and if changed the 5 in your query to 31 to cover whole month, i am getting blanks rows error. Also can you guide to create for entier year.
"Source = List.Dates(#date(2018, 1, 1),31, #duration(1, 1, 0, 0)),"
Hi nagoor,
To cover dates for entier year, please try this:
Source = List.Dates(#date(2018, 1, 1),365, #duration(1, 1, 0, 0)),
Regards,
Yuliana Gu
