Page 288 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

What are the best-advanced Graphs in Excel?
• Sankey diagram

• Likert Scale chart
• Comparison Bar Chart
• Gauge Chart
• Multi-Axis Line Chart
• Sunburst Chart

• Radar Chart
• Radial Bar Chart
• Box and Whisker Chart
• Dot Plot Chart

What is Power Query?

Power Query is a business intelligence tool available in Excel that allows you to import data from many different
sources and then clean, transform and reshape your data as needed.
It allows you to set up a query once and then reuse it with a simple refresh. It’s also pretty powerful. Power Query
can import and clean millions of rows into the data model for analysis after. The user interface is intuitive and well
laid out so it’s really easy to pick up. It’s an incredibly short learning curve when compared to other Excel tools
like formulas or VBA.
The best part about it, is you don’t need to learn or use any code to do any of it. The power query editor records
all your transformations step by step and converts them into the M code for you, similar to how the Macro recorder
with VBA.
If you want to edit or write your own M code, you certainly can, but you definitely don’t need to.
What Can Power Query Do?
Imagine you get a sales report in a text file from your system

on a monthly basis that looks like this.
Every month you need to go to the folder where the file is uploaded and open the file and copy the contents into
Excel. You then use the text to column feature to split out the data into new columns.
The system only outputs the sales person’s ID, so you need to add a new column to the data and use a VLOOKUP
to get the salesperson associated with each ID. Then you need to summarize the sales by salesperson and
calculate the commission to pay out.

275

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77