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

  • starmoonknight's avatar
    starmoonknight
    Helper II
    9 years ago

    Thanks Anonymous

     

    I managed to create a table with future dates based on what ankitpatira provided, which was quite similar to your modification. Just wondering, whether it is possbile to use the latest date of the current Date table (in the example you provided, "8/31/2016") as the Source rather than a manual input so that I don't have to do the same thing every month.

     

    BTW, I've noticed that both you and ankipatira defined start date, end date, and interval for the date list. Is there a way to only define one of the date, and add/minus a specific number of intervals.

     

    Anyway, I'm using this method to meet the requirements for now, hopefully there is (or will be) a way to auto add one month to the current Date table. Thanks again.

     

    Olivia

     

    Olivia

    • Anonymous's avatar
      Anonymous
      Not applicable
      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 Anonymous, I think I could append the list to the Date table by a click of button for now and spend some time learning MDX to understand the syntax.

         

        Just wondering does it mean that there's not way to use the lastest date of the Date table as the source date to create that list, and I could only manuly define one of the date?

         

        Cheers