Appending Historical Data in Microsoft Power BI

How to Append Historical Data to Power BI

If you are using a Power BI connector for a dashboard, every time new data is generated, your dashboard will be updated.

In this article, we will go through three methods to append data in Power BI.

We will show you an example of how you can keep your past data into your Power BI dataset so that you can see both historical and new data in your real-time dashboard.


Method 1: Using a connector that caches Data

For this case, you can use a connector such as, that caches data the first time you can connect a Data source, and then you can query all the data you need up to how much the API allows.

To do this you would just need to connect your data source, for this example will use Google ads.


1. Go to’s page and create an account

Free trial


2. Select Google ads and connect your account to

grant access to facebook ads


3. Choose the fields, the data range you need to query and press on Power bi Desktop Logo

data source and date range


4. Copy your API Key and then in Power bi desktop, press Get data from the web and copy your API URL there



Now you can query historical in seconds.


Method 2: Appending Data using M query

For this method, you would need to have basic knowledge of M query.


1. Duplicate the table that you want to start with by right click on the main table

duplicate table PBI


2. In the the new table go to advanced query editor and copy the query

M query PBI



3. Copy the query in the advanced editor of the original table, delete the duplicated table and now refresh and the data should be appended.


Method 3: Creating a custom M query to append Data

This method is more complicated and would require an advanced knowledge of M query.


First create a function named “GetData” to get data of a specific date from that site.


(date1 as date) as table =>
Source = Web.Page(Web.Contents(""&Date.ToText(date1,"yyyy-MM-dd"))),
Data = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}})
#"Changed Type"


Then use the formula below to create a new query to get all data from the date range which you can specify in the query.


Source = List.Dates(#date(2017, 12, 1), 10, #duration(1, 0, 0, 0)),
ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Renamed = Table.RenameColumns(ToTable,{{"Column1", "Date"}}),
Added = Table.AddColumn(Renamed, "Custom", each GetData([Date])),
#"Expanded Custom" = Table.ExpandTableColumn(Added, "Custom", {"Currency code ▲▼", "Currency name ▲▼", "Units per USD", "USD per Unit"}, {"Custom.Currency code ▲▼", "Custom.Currency name ▲▼", "Custom.Units per USD", "Custom.USD per Unit"})
#"Expanded Custom"



Conclusion: Appending Historical Data in Power BI

There are many ways to keep historical data in power while getting daily updates, but it is not advised to use these methods for big volumes of data.

To use the methods that we spoke about in the article, you would need to have basic and intermediate knowledge of M Query to be able to manipulate your data, by adding, removing or formatting your tables and columns.

That’s why we advise you to use’s tool, because with’s software your data will be cached and can be queried at any time with any coding or hassle.

Start your free trial today.


You May Be Also Interested in:

Free Power BI Templates in 2023

Guide to Power BI for Digital Marketing

Power BI Dashboard Samples for 2023