Forum Discussion

Raaz's avatar
Raaz
Icon for Helper II rankHelper II
6 years ago

Add row to a custome table after each refresh

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 quer...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Raaz
    Icon for Helper II rankHelper II
    6 years ago

    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

    • v-alq-msft's avatar
      v-alq-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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.

      • Raaz's avatar
        Raaz
        Icon for Helper II rankHelper II
        6 years ago

        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,