Forum Discussion

starmoonknight's avatar
starmoonknight
Helper II
9 years ago
Solved

How to add rows to a date table in Query Editor

Hi all,   I'm a PBI user, and have been playing around for a while, but still not very familiar with "M" (sick of all the different languages I have to learn, MDX, M, DAX ...).   I'd just go stra...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi starmoonknight,

    Firstly, you can define only one of the date and add a specific number of intervals as follows.

    List.Dates( #date(2016,9,1), 30, #duration(1,0,0,0))


    Secondly, you can open advanced editor and directly modify the source codes of your date table to add rows for it. Below is an example for your reference.

    let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM3sNA3slSK1UFwjQ1QuYZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Source1 = Table.FromList(List.Dates( #date(2016,9,1), 30, #duration(1,0,0,0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Column1", type date}}), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Column1", type date}}), #"Appended Query" = Table.Combine({#"Changed Type", Source1}), #"Changed Type2" = Table.TransformColumnTypes(#"Appended Query",{{"Column1", type date}})in #"Changed Type2"



    Thanks,
    Lydia Zhang

starmoonknight's avatar
starmoonknight
Helper II
9 years ago

Thanks a lot! ankitpatira Just tried, it's a feasible solution as I don't have to manually enter data using your method :)

 

BTW, as I've already got a Date Table (with only one column that includes consecutive dates up to now), is there a way I could edit the query upon that Date Table to simply append new dates (in the futher month) to it.

 

The psudo code according your current approach is kind of like this: 

create a list of dates from the last date of Date Table to one month after the last date

convert the list to a New Date Table

append the New Date Table to the Date table

 

Thanks in advance~

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi starmoonknight,

I make some changes about the steps that ankitpatira provides, check if the following steps meet your requirement.

1. There is a Date table containing the following date values.


2. Click New Source -> Blank Query and in the middle bar next of fx type in date 9/1/2016.
3. Click on fx to create custom step as below using this formula: = List.Dates(Source, Number.From(#date(2016,10,1))- Number.From(Source) ,#duration(1,0,0,0)).
4. Right click the list you get from previous step -> To Table.
5. Append the newly created table with your original date table.


To add dates for next month in the Date table, you can repeat the above steps. Just change end date and source date in the step2 and step3.

Thanks,
Lydia Zhang