[et_pb_section admin_label=”section”][et_pb_row admin_label=”row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]
Power Query’s MERGE feature: a VLOOKUP on steroids
Is VLOOKUP one of your favourite functions in Excel? No doubt it is quite useful, but when your data is constantly changing, it is a drag (no pun intended) to keep updating your VLOOKUP calculations to take into account new rows or columns.
With Power Query’s MERGE feature, you have all the power of the VLOOKUP function plus the ability to return multiple columns from the lookup table. It will also always apply to every row in your table, even if it changes and you don’t have to worry about those pesky errors when there are no matches. It’s as simple as selecting a column from each table you want to lookup and checking the columns from the lookup table you want to run. No hassle.
[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row admin_label=”Row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]
How To Fill down
Are you sourcing data from reports that have line items grouped with totals, but no column that you can use to group or filter these line items With Power Query’s Fill Down feature, you can add a column with the name of the group and populate the line item rows with this value. Now you have a column identifying which group the line item falls under and can now use it to group or filter your data.
[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row admin_label=”Row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]
How to Pull Data from a Website
There is a lot of public data available on the web. Exchange rates, petrol prices, etc. Pulling this data from the web using Power Query is as simple as entering the webpage address and selecting the data you want to import from the page. Now you can have always up to date exchange rate data that you can join to your sales data to work out the value in other currencies. No need to have to go back to the site to copy the latest data when you need it. Power Query will always pull the latest values when you refresh your report.
[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row admin_label=”Row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]
Unpivot multiple columns into a single column
Do you deal with spreadsheets that have content spread across multiple columns resulting in you having to manually transpose these columns into a single column? Using Power Query it is as simple as a selecting the columns you want to unpivot, right click and select Unpivot Columns.
[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row admin_label=”Row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]
Combining the data from multiple files into a single result
Do you often have data spread over multiple files that you have to combine into a single result so you can create reports from the combined result? For example, It’s year end and your sales data is spread across 12 monthly reports that you have to manually append into a single file in order to produce your year end reports.
Don’t waste time copy pasting the data from each file in order to create a single spreadsheet with all the data. In Power Query it is as simple as selecting the folder that contains the files and let Power Query append the data from each into a single result.
[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row admin_label=”Row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]
Excel Power BI Essentials training course
Do you want to learn more about Power Query? e-magination InfoSolutions is hosting an Excel Power BI Essentials training course which will teach you how to use Excel and Power Query to perform tasks like this and much more.
Check out our event page and register here.
[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]