Why learning Power Query and Power Pivot is essential for all Excel users
Excel is fantastic. But you are really missing out if you do not use Power Query and Power Pivot.
Power Query is inbuilt in Excel under Data > Get and Transform Data.
https://powerquery.microsoft.com/en-us/
It is an ETL tool that can connect to hundred of data sources, has 300+ transformations and requires no coding. It works on various Microsoft products, including Power BI, Excel, CDS, Power Automate and Analysis Services.
Why is it so useful?
There are common challenges all Excel users face:
a. Not being able to handle large data in Excel as crashes happen
b. Consolidating multiple files from a folder is a chore
c. Reading data from PDFs does not usually work
d. Applying formulas on large datasets takes time
e. Reconciling multiple spreadsheets is a hassle
f. Actions in Excel are not recordable (VBA Macros is not suitable for the average Excel user)
g. Split data into rows is not possible in Excel
h. Flash Fill can be erratic, does not given a preview and does not show any formula for the result
Power Query solves all these challenges!
Power Pivot is a COM Add-in and is bundled with Excel. You have to enable it: File > Options > Add-ins > Manage COM Add-ins.
Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.
Why is it so useful?
There are common challenges all Excel users face:
a. Combining multiple datasets from a workbook / multiple workbooks into a combined Pivot Table is not possible
b. Writing formulas in Pivot Tables is impossible
c. VLOOKUP has to be used to combine different datasets
Power Pivot solves all these challenges!
Watch here:
https://www.youtube.com/watch?v=msp-S-HaP0w
