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 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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi starmoonknight,

I haven't found any method that use the lastest date of the Date table as the source date to create that list. You just need to enter first date of each month as the source date to create that list, it is also convenient in my opinion.

Thanks,
Lydia Zhang

  • starmoonknight's avatar
    starmoonknight
    Helper II
    9 years ago

    Thanks Anonymous

     

    Yes, it's not at all incovenient, and I'm doing it this way now! Probably that's why it's not necessary to be supported, and I guess the underlying algorithm may not be as straight forward as it sounds like.

     

    Cheers