Custom table with auto added columns with fix time period

[Anonymous]

Hi all,

I'm currently trying to capture data trend for a specific column( COLUMN A) that is pulled from an ODATA data source (Table X).

I would like to create a new custom table(Table Y), and take a snapshot of the Column A in Table X with preset timing(every day/week/month), store it as column 1 in table Y. The table Y will have column add to it automatically everytime when preset time is triggered. 

How should I do this?

For example, I would like to capture table X ColumnA everyday, take 9 AM as snapshot time.

So day 1:

Table Y will have new column 1 to store current Column A value from Table X

day 2:

Table Y will have new column 2 to store current Column A value from Table X, while column 1 remains no change.

day 3:

Table Y will have new column 3 to store current Column A value from Table X, while column 1 and 2 keep the previous 2 days of data.

....

How do I add new column automatically?

How do I set the time period in between snapshot?

And I would like to name my column 1 2 3 with the date of snapshot, how do I do that automatically?

Is there anywhere I can define my own logic/code to do this?

Thanks so much for help in advance.

[v-yulgu-msft]

Hi @Anonymous,

As of now, it is not supported to add new custom columns automatically and keep the history values of one column.

Regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft

Here is what I am trying with the Power Query M:

let
Source = OData.Feed("**********************************************"),
opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(opportunities_table,{"modifiedon", "ppp_probability", "name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"name", "modifiedon", "ppp_probability"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"modifiedon", Order.Descending}}),

#"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "ppp_probability", "ppp_probability - Copy"),

Today = DateTime.ToText(DateTime.LocalNow(),"yyyyMMdd"),   ---get current date

   EndweekDay = DateTime.ToText(Date.EndOfWeek(DateTime.LocalNow(),Day.Sunday),"yyyyMMdd"),

--get the date of end of the week

boolRefresh = if(Today = EndweekDay) then true else false,
 ---Here try to determine if current date is end of week

 #"Column"= if(boolRefresh = true) then Table.AddColumn(#"Duplicated Column",Today, each [ppp_probability]) else  #"Duplicated Column" --- if today is end of week, add a column to record the probability column, if not, don't add extra column

in

 #"Column"

I tried with above query, it can create new column when it's end of the week, but if it's normal days, there will be no additional column, and previous recorded additional column will be removed as well. Any suggestion for this?

Another concern here is, once I duplicate and add a new column in the table, if the original column [ppp_probability] changed, will the duplicate column update as well? Or could the duplicate column persist the value at the time of duplication? Thank you.

[v-yulgu-msft]

Hi @Anonymous,

Per my research, your requirement looks like unachievable right now. Power BI does not have incremental load at present. Also, it doesn't provide a snapshot functionality to keep old data when you refresh dataset.

Data Snapshots

Incremental Data Loads

Best regards,

Yuliana Gu

[Anonymous]

Hi, 

I realize there are some update regarding incremental refresh recently.

So how about snapshot in fix interval to keep old data? Any suggestion based on the latest release?

If it's still not possible, I would like to build a separate database to do that.

I have some budget to purchase Azure subscription, any recommendation of which type of storage shall I choose? There are quite a number of different storage. I wonder if any of it provide snapshot as a default feature.

Thanks.

[Anonymous]

Hi @v-yulgu-msft,

Thank you for the reply. May I know do you have any recommendation for alternatives for the task mentioned above?

I was trying to use DAX to achieve this, but I realize it doesn't allow me to do quite a number of things.

For example, AddColumns in DAX doesn't allow me to use Format(Today(),"YYmmDD") as the name parameter, I can't retreive column value from another table etc..

Can I write a script (R script for example?) to perform the task above? I'm new to scripting in POWER BI as well.

Thank you.

[Anonymous]

More information:

I have added a new table called " audit tracking" (table Y), however I only found "enter data" which allows us to enter data manually, I didn't find anywhere that allow me to copy data from other table columns automatically.