Add row to a custome table after each refresh

Question

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

mwegener · Answer

Hi&nbsp;Raaz&nbsp;
&nbsp;
this is not possible.
Measures are calculated at runtime.
&nbsp;

anonymous · Answer

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

Thanks for your reply.&nbsp;Your solution is overwriting the previous records. I need to preserve each row with each hour and previous calculated measure.&nbsp;Expected Output&nbsp;Measure&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hour12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (i.e.11AM)9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (i.e. 12AM)8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (i.e.&nbsp;&nbsp;1PM)----&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -----&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(i.e. 5PM)&nbsp;is this possible to update my table like this with each hourly refresh?&nbsp;Thanks

v-alq-msft · Answer

Hi,&nbsp;Raaz&nbsp;&nbsp;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.&nbsp;&nbsp;You may click 'Edit Query', go to Query Editor, create a new blank query, click 'Advanced Editor', input codes as below.&nbsp;&nbsp;(value as number)=&gt;
let
NewDate = DateTime.LocalNow(),
Result = Table.FromRecords({[Value = value,Date = NewDate ]})
in
Result&nbsp;&nbsp;&nbsp;Then you may input value to generate a record with current datetime.&nbsp;Finally you need to make one generated query selected, go to 'Home' ribbon, click 'Append Queries', set as below to add generated queries together.&nbsp;&nbsp;Result:&nbsp;Best RegardsAllan&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mwegener · Answer

Hi&nbsp;Raaz&nbsp;,
&nbsp;
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-data-to-it-using-incremental-refresh/
&nbsp;
https://www.thebiccountant.com/2020/04/13/create-a-load-history-or-stage-in-cds-instead-of-incremental-load-in-power-bi/
&nbsp;