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 windsor.ai, 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 Windsor.ai’s page and create an account
2. Select Google ads and connect your account to Windsor.ai
3. Choose the fields, the data range you need to query and press on Power bi Desktop Logo
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
2. In the the new table go to advanced query editor and copy the query
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 =>
let
Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date="&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}})
in
#"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.
let
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"})
in
#"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 Windsor.ai’s tool, because with windsor.ai’s software your data will be cached and can be queried at any time with any coding or hassle.
You May Be Also Interested in:
Free Power BI Templates in 2023