Excel Sales Report (Part 1)-Import Data

This series will follow the steps of creating a Sales Report.

Goal is to create a sales report with the following objective:

· Year/month sales trend visualizations

The total steps of this process to be broken down:

· Use power query to import data

· Use formulas

· Use standard pivot table to create report

· Use excel line chart

(Step 1) Import Data:

The data for this project consists of a .txt file that contains — date, productid, sales rep, and units — and two small lookup dimension tables dProduct and dSalesRep.

Sheet before:

Sheet before adding importing data

First I went to the data tab of the ribbon and selected ‘From text/csv’. I then selected the sales.txt file and clicked ‘Import’.

import data box to select file and import

After selecting import the preview screen opened up.

I selected the ‘Transform Data’ button. The power query editor opens up. I checked the column headers and data types. All looked good. Changed name of table to fSales table to indicate it is a fact table.

Selected close and load to, existing sheet.

The final screen

Data from .txt file successfully uploaded