Power Query
Importing Data
Data >>> Get Data (PowerQuery) >>> From Text/CSV
- You can choose the type of delimiter
- Either Load (If you want the data as is) or Transform (If you want to modify the data) the data
Transforming Data
- Centre where you can preview data is called ‘Data View’
- Right side you can see the Query Settings where all the transformations are listed.
– If its not showing up
View >>> Query Settings
- Left side is the Query List – you can see all the different connections you make
Transformations
Removing Header Rows
Home >>> Remove Rows >>> Remove Top Rows >>> Specify how many rows you want to remove
Switching first column from data to column headers
Home >>> Use First Row as Headers
Remove unnecessary columns
Select all columns that you do not need with ‘Command ⌘’
Home >>> Use First Row as Headers
Unpivoting
Basically you are switching the columns to rows. Select all columns that you do not want to unpivot with ‘Command ⌘’
Transform >>> Unpivot Column ↓ >>> Unpivot other columns
Renaming column headers
Double click the column headers and rename within the ‘Data View’
Removing Blanks
Column Header ↓ >>> Remove Empty
Defining data types
Select all columns that you want to define with ‘Command ⌘’
Home >>> Data Type: Whole number >>> Whatever format you want
