Adding new rows when refreshing

[Rusciano]

Hi Community,

I am about losing my head to solve the following issue. It would be very easy for members which usually use Power.Query but not for me that a never do it before (I have always used DAX !!!). 

I computed a very simple query = DateTime.LocalNow() in order to retrieve the last update data time. By clicking on "Refresh" button the date time update correctly. Thus, the issue is that the query overwrite the previous record. Consequently, I always have one column with  just one row. You can check the attached screen to clarify my status of art.

Conversly, my goal is to have one column with how many rows as the refreshes are. 

[v-xiaosun-msft]

Hi @Rusciano ,

You need to add a column or index column to show the position of the rows as below.

Then you can use Table.InsertRows in Power Query. Below is my formula, you can reference it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLVNzTSNzIwMlKwsDIxszIxUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dimension = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dimension", Int64.Type}, {"Date", type datetime}}),
    Custom1 = Table.InsertRows(#"Changed Type",1, { [Dimension = 2, Date = DateTime.LocalNow()] })
in
    Custom1

Final output:

Then you can click the "Refresh" button. You can reference the following link.

Table.InsertRows - PowerQuery M | Microsoft Learn

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[Rusciano]

Dear v-xiaosun-msft, thank you for your time, but unfortunely the query you suggested doesn't work since when I refersh the query does not add a new row recording the updated data time but it only overwrite the last data time record update.

To be clearer my issue is to have one column with how many rows as the refreshes are. 

Thank you in advance for your support.