2.4.2 Importing Locally Stored Data from Data Boutique into Power BI

Updated by Andrea Squatrito

Importing Locally Stored Data from Data Boutique into Power BI

Once your Data Boutique files are copied from AWS S3 to a local directory, you can easily import them into Power BI to create visualizations and analyze trends. Power BI is a business intelligence tool that allows you to connect to various data sources, transform raw data, and create interactive reports and dashboards. This article covers the steps to bring your local CSV files into Power BI, transform the data as needed, and set up a refresh schedule for consistent updates.

Prerequisite: Ensure that your files are regularly copied from AWS S3 to your local storage. For details, refer to Copying Locally and Managing Local Storage of Data from AWS S3 on Data Boutique.

Step 1: Connect Power BI to Your Local CSV Data Directory

  1. Open Power BI Desktop:
    • Launch Power BI and go to the Home tab.
  2. Load Your CSV File:
    • Click on Get Data > Text/CSV.
    • Navigate to the directory where your CSV files from Data Boutique are stored, select the file you want to import, and click Open.
  3. Use Power Query to Transform Data:
    • After selecting the CSV file, Power BI opens the Power Query Editor, allowing you to preview and transform your data.
    • Here, you can remove unnecessary columns, filter rows, or perform any data transformations to prepare the data for visualization.
  4. Load Data to Power BI:
    • Once you’ve finished editing, click Close & Apply. Power BI will load the data into the Data Model, making it available for building reports and dashboards.
  5. Combine Multiple Files (Optional):
    • If you’re working with multiple CSV files with the same schema, you can combine them within Power Query:
      • Select Folder as the data source and point to the directory with all your CSV files.
      • Power BI will prompt you to Combine the files, consolidating data from all files in that folder.

Step 2: Visualize Your Data

  1. Switch to the Report View:
    • Go to the Report View to start building your visualizations.
  2. Explore Data Fields:
    • Drag and drop fields from the Fields Pane to the Canvas to create charts, tables, and other visual elements.
    • Use filters, slicers, and calculated columns to refine your analysis.
  3. Save Your Report:
    • Save the Power BI file (in .pbix format) to keep your work.

Step 3: Schedule Data Refreshes in Power BI

To keep your Power BI reports in sync with the latest data, you can set up a refresh schedule if you’re using Power BI Pro with Power BI Service.

  1. Publish to Power BI Service:
    • To set up a scheduled refresh, publish the report from Power BI Desktop to the Power BI Service.
    • Go to File > Publish > Publish to Power BI and select a workspace.
  2. Set Up a Data Gateway (If Needed):
    • If the data resides on your local machine and Power BI Service needs to access it, set up an On-premises Data Gateway. Download and install the gateway, and configure it to allow Power BI Service to access your local files.
  3. Configure Scheduled Refresh:
    • In Power BI Service, go to Settings > Datasets and select your published dataset.
    • Under Scheduled Refresh, configure the refresh frequency (e.g., daily) to align with the frequency of your local storage updates.
    • Enter your gateway connection information if required, and save the settings.

Note: If you’re using Power BI Desktop without a Pro account, you’ll need to manually refresh the data by reopening the file and applying changes when new data is available.

Step 4: Verify Data Connection and Refreshes

After scheduling the refresh, verify that Power BI correctly updates the data:

  • Check Dashboard Updates: Confirm that new data appears in your visualizations after each scheduled refresh.
  • Monitor File Changes: Ensure that your local directory includes the latest files from Data Boutique so that Power BI has access to the most current data.

Conclusion

Following this guide enables you to seamlessly import Data Boutique CSV datasets into Power BI, creating interactive and dynamic visualizations. With scheduled refreshes, you can automate the data update process and ensure your reports always reflect the latest insights, supporting data-driven decision-making in your organization.

How did we do?