Power Query for Excel

With Power Query, you can

• Find and connect data across a wide variety of sources.

• Merge and shape data sources to match your data analysis requirements or prepare it for further analysis and modeling by tools such as Power Pivot and Power View.

• Create custom views over data.

• Use the JSON parser to create data visualizations over Big Data and Azure HD Insight.

• Perform data cleansing operations.

• Import data from multiple log files.

• Pull data into Power Pivot from new data sources, such as XML, and File Folders as refreshable connections.

• With Power Query 2.10 and later, you can share and manage queries as well as search data within your organization

A step by step manual on how to use Power Query in excel and apply it in humanitarian context can be downloaded here.

Samples for using Power Query in humanitarian context

• 3W data consolidation and processing (Example).

• Process Cash data (Example).

• Kobo data consolidation and processing (Example).

• Connect, import and visualize in Power BI, FTS data (Example).

• Import and process The Armed Conflict Location & Event Data Project (ACLED) data (Example).

Useful tools shared from the field

The following has been contributed by Christian Cricboom:

1. Power Query - Calendar Function
2. Power Query - Combine Rows
3. Power Query - Get Previous Row Value
4. Power Query - Merge All Columns But First One
5. Power Query - Running Total
6. Power Query - How to import data exposed in multiple pages in a website
7. Power Query - Remove Punctuation from text
8. Power Query - Ranking Partition
9. Power Query - How to extract data from a .zip file