Scribd
Upload
58 views3 pages

Data Using Power Query

The document outlines the steps to analyze sales data using Microsoft Excel Power Query, including connecting to data, transforming it by cleaning and adding new columns, combining data from multiple sheets, and loading the final dataset. It also provides instructions on creating a new column for the month based on sales dates and suggests further analysis options like PivotTables and visualizations. The overall goal is to facilitate data analysis and reporting for sales trends across different months.

Uploaded by

john wick
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
58 views3 pages

Data Using Power Query

The document outlines the steps to analyze sales data using Microsoft Excel Power Query, including connecting to data, transforming it by cleaning and adding new columns, combining data from multiple sheets, and loading the final dataset. It also provides instructions on creating a new column for the month based on sales dates and suggests further analysis options like PivotTables and visualizations. The overall goal is to facilitate data analysis and reporting for sales trends across different months.

Uploaded by

john wick
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Sales Data Using Microsoft Excel Power Query

1. Connect:
o Step 1: Open a new workbook in Excel.
o Step 2: Save the above data into three sheets named January, February, and
March within an Excel workbook.
o Step 3: Go to the Data tab in Excel and click Get Data → From Workbook.
o Step 4: Select the Excel file that contains the data. Power Query will open,
showing all the sheets in the file.
o Step 5: Choose the January, February, and March sheets to load into Power
Query.
2. Transform:
o Step 1: Power Query will display the tables from the selected sheets.
o Step 2: In the Power Query Editor, you can remove the "Comments" column (if
not needed), change the data type of Sales Amount to Currency, and rename
columns as needed (e.g., "Sales Date" → "Date").
o Step 3: Remove any blank rows in the dataset by filtering out null values from
the "Sales Amount" column.
o Step 4: Optionally, create a new column for Sales Tax: Go to Add Column →
Custom Column and enter the formula:

= [Sales Amount] * 0.1

This will calculate 10% of the Sales Amount as Sales Tax.

3. Combine:
o Step 1: After transforming each sheet (January, February, and March), you need
to combine them.
o Step 2: Go to the Home tab in Power Query and click Append Queries →
Append Queries as New.
o Step 3: In the Append Queries dialog, select the three tables: January, February,
and March to append them into a single dataset.
4. Load:
o Step 1: Once the data is combined, click Close & Load to load the final dataset
into a new sheet in Excel.
o Step 2: The data will appear in a new sheet, and you can now perform analysis or
create PivotTables, charts, or reports using the combined data.

• Add a New Column for Month:

• In the Power Query editor, go to the Add Column tab and click on Custom Column.
• In the formula box, use the if statement to manually assign the month based on the Sales
Date column, assuming your Sales Date column is consistent and contains valid dates:
= if Date.Month([Date]) = 1 then "January"
else if Date.Month([Date]) = 2 then "February"
else if Date.Month([Date]) = 3 then "March"
else "Unknown"

• This formula assigns the correct month (January, February, March) based on the Sales
Date column.

• Click OK to add the Month column.

Final Combined Data (After Loading):


What to Do Next:

Now that the data is combined into one table, you can:

• Create a PivotTable to summarize sales by region or employee.

10000

9000

8000

7000

6000

5000 Sum of Sales Tax

4000 Sum of Sales Amount

3000

2000

1000

0
Jan Feb Mar

• Build visualizations like bar charts or line graphs to track sales trends over time.

Sales Trend Over Time


3000

2500

2000

1500

1000

500

Sales Amount Region Employee Sales Tax Month

You might also like