Categories
Blog

Excel PowerBI Essentials

[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”]

How To fill down values into blank cells

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.

picture1

[/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”]

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.

picture2

[/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.

picture3

[/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.

picture4

[/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.

picture5

[/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 PowerBI Essentials Course

Many have seen or heard of Microsoft Power BI, but not many are equipped with the essential skills to become an empowered data citizen. The craft of converting data into insight has a shiny new tool box in the form of Excel Power BI add-ins viz. Power Query and Power Pivot for Excel 2016, 2013 and 2010. All you need are the skills to utilize this BI tool box within Excel to rapidly turn data into business advantage. If an essential part of your role involves deriving insight from data, whether you are a BI professional or financial manager, then this course is for you. This course is hosted by e-magination InfoSolutions, a licensed/official provider of Osmosis training courses.

In this training course we build an end-to-end solution using Power Query and Power Pivot. The areas we cover are:

  • What is Power Query and Power Pivot and how it empowers the data organization
  • Use Power Query to discover and shape data for analytical purposes
  • Use Power Pivot and Data Analysis Expressions (DAX) to create rich analytical data models

By the end of the day we will have a complete solution and worked through many of the essential technical skills, as well as explore techniques for building personal BI solutions. Workstations, samples and training guides will be provided.

[/et_pb_text][et_pb_button admin_label=”Button” button_url=”https://www.quicket.co.za/events/22653-excel-power-bi-essentials-training-workshop-cpt” url_new_window=”on” button_text=”Book your tickets” button_alignment=”center” background_layout=”light” custom_button=”off” button_letter_spacing=”0″ button_use_icon=”default” button_icon_placement=”right” button_on_hover=”on” button_letter_spacing_hover=”0″] [/et_pb_button][/et_pb_column][/et_pb_row][/et_pb_section]