Loading historical data in Power BI helps draw rich marketing insights. However, you must know how to do it right.
If you are an advertising agency or working with a marketing team at a large company, you must be familiar with Power BI’s challenge with high data volume. If the number of rows exceeds 10,000 from a single query Power BI runs into issues.
Storing a historical CSV export of marketing data in Power BI locally can help you overcome this problem. You can then create a new table that draws data from both web-based and local CSV-based tables.
Here’s how you can do it in 5 easy steps.
Steps for uploading data into Power BI
1. Download historical data from your query manager
Run a query for the date range and data on your query manager. If the data you are looking for is voluminous or the date range too big, you may want to break it into multiple CSV files.
2. Set up web-based data for automatic incremental update
For web-based data, copy the URL from your query manager and save it in a notepad. In the URL parameters, set the end date to today. Ensure the start date parameter in the URL of your web-based data is right after the last date in your CSV files.
3. Import the CSV files with historical data into Power BI
Under the Home tab in Power BI, click on Get Data and choose Text/CSV. Upload the relevant CSV files. Once the data is imported, name the table appropriately to distinguish it from incremental web-based data.
4. Import web-based table
Navigate to Home > Get Data > Web. In the pop-up that appears, select basic and choose basic. Paste the URL obtained in step 2 and click OK.
5. Create a UNION table
Under Table Tools, choose modeling and create a UNION of tables from CSV files and the one on the web.
The syntax for it is:
UNION(<table_expression1>, <table_expression2> [,<table_expression>]…), where table_expression is the name of the table.
While the historical data table remains constant, the web-based table will automatically refresh with a single click. The process of loading and using historical data into Power BI is fairly straightforward. However, it has a few limitations.
Limitations in loading historical data into Power BI
- You may have to run multiple queries if your data exceeds 1M rows. The queries can be split by days, weeks, months, quarters, or years.
- If your query manager cannot retrieve data in 30 minutes, it would time out. You will have to reduce the date range by splitting data to bring retrieval time to under 30 minutes.
- Similarly, you will have to slice the date range if the size of data exceeds 1GB.
- Although there isn’t a limit on files you can upload, keeping the cumulative size under 1GB helps you process data faster.
Using a cloud-based data warehouse like BigQuery, Snowflake, or Azure Synapse, can help you overcome most of these limitations. These warehouses do the heavy lifting of data and free up computing power for Power BI.
Windsor.ai’s web data connector makes importing web-based data completely seamless.