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 straight to the point. I've got a one column Date table that captures the transation dates in the ERM. However, I'd like to add extra records to this table to include dates in the future.

 

Currently, I just use Enter Data to create a list of dates of the next month and append that sheet to the Date table. However, I have to manually edit that sheet every month.

 

As I'd like to do other ETL processing on the Date table, is there a way to add rows to the table in query editor (i.e. using M language). Say, always add a month to the table.

 

For instance, this is the current Date table:

...

29/08/2016

30/08/2016 

31/08/2016

 

I'd like to have a table like this:

... 

29/08/2016

30/08/2016 

31/08/2016

01/09/2016

02/09/2016

...

29/09/2016

30/09/2016

 

Any help is appreciated and don't laught at me if it's to easy a quesion.

  • 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

10 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    starmoonknight Not sure if i understood your business problem but if what you wanted is to have a table / column with all the dates from your start to end date then simply in power bi desktop go to Modelling tab -> New Column / New Table -> DAX as below.

     

    MyDates =CALENDAR (DATE(2016,1,1); DATE(2016,12,31))

    • starmoonknight's avatar
      starmoonknight
      Helper II
      9 years ago

      Thanks ankitpatira

       

      I've got a table using the way you mentioned, but a culculated table cannot be merged to another table. Is there a way to do similar stuff in query editor before loading as I'd like to do some tranformation and reshaping

      • ankitpatira's avatar
        ankitpatira
        Community Champion
        9 years ago

        1. Go to power bi desktop query editor.

         

        2. Click New Source -> Blank Query and in the middle bar next of fx type in date '01/01/2016'

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        3. Then click on fx to create custom step as below using this formula. = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))

         

         

         

         

         

         

         

        4. Then right click the list you get from previous step -> To Table.