How to add rows to a date table in Query Editor

[starmoonknight]

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.

[ankitpatira]

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

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]

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.

[starmoonknight]

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~

[v-yuezhe-msft]

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]

Thanks @v-yuezhe-msft

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

[v-yuezhe-msft · Accepted solution]

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]

Thanks a lot @v-yuezhe-msft, 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

[v-yuezhe-msft]

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]

Thanks @v-yuezhe-msft

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