Add row to a custome table after each refresh

[Raaz]

Hi Community Experts,

I have a calculated measure. I want to write this measure to Power BI table(New Custome table)  with current system time hour after each refresh as a new row. i.e. when query is refreshed after every hour, the calculated measure should be inserted with the current hour as a new row. Is this possible in Power BI ?

Thanks for your help

[Anonymous]

Create a blank dataset then in the advanced editor 

let
Source = Table.FromValue(DateTime.LocalNow() ),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "RefreshDate"}})
in
#"Renamed Columns"

this should store the value when the dataset is refreshed. 

YOu can also create new column in the existing table

[Raaz]

Thanks for your reply.

Your solution is overwriting the previous records. I need to preserve each row with each hour and previous calculated measure.

Expected Output

Measure            Hour

12                          11                                           (i.e.11AM)

9                             12                                          (i.e. 12AM)

8                            13                                           (i.e.  1PM)

----                       -----                                          ----

2                           17                                             (i.e. 5PM)

is this possible to update my table like this with each hourly refresh?

Thanks

[mwegener]

Hi @Raaz ,

take a look at the following concepts:

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...

https://www.thebiccountant.com/2020/04/13/create-a-load-history-or-stage-in-cds-instead-of-increment...

[Raaz]

Very nice post!!!

I tried to edit this for my requirement which is incremental refresh by hour instaed of each day. But in the incremental refresh options, refresh rows gives choice for only days,month, quarter and year not hour. Is there any way to specify refresh row by hour ?( i am not talking about scheduling in powerbi.com but incremental refreh in power bi Desktop)

Thanks,

[mwegener]

Hi @Raaz ,

incremental refresh in Power Bi Desktop is not possible.

In the Power BI service, you must specify each execution time individually.

You can perform multiple dataset refreshes daily, which might be necessary if the underlying source data changes frequently. Power BI limits datasets on shared capacity (Power BI Pro) to eight daily refreshes. If the dataset resides on a Premium capacity, you can schedule up to 48 refreshes per day in the dataset settings.

https://docs.microsoft.com/en-us/power-bi/refresh-data#data-refresh

[v-alq-msft]

Hi, @Raaz 

It is unavailable to directly insert a new row to a query generated previously. As a workaround, you may generate several queries and then append them together. 

You may click 'Edit Query', go to Query Editor, create a new blank query, click 'Advanced Editor', input codes as below.

(value as number)=>
let
NewDate = DateTime.LocalNow(),
Result = Table.FromRecords({[Value = value,Date = NewDate ]})
in
Result

Then you may input value to generate a record with current datetime.

Finally you need to make one generated query selected, go to 'Home' ribbon, click 'Append Queries', set as below to add generated queries together.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @Raaz 

this is not possible.

Measures are calculated at runtime.