Calculated Table - Calendar function with 15 minutes interval

[nagoor]

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? 

[v-yulgu-msft]

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

[nagoor]

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)),"

[v-yulgu-msft · Accepted solution]

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

[nagoor]

Any new idea can help me to fix this issue