Chandoo

Merry Christmas & Happy New Year 2021

Thu, 24 Dec 2020 10:00:06 GMT

Hello readers and fans…

On behalf of my family, our staff, volunteers & well-wishers, Let me wish you a very happy Christmas & prosperous new year 2021. Here is a small holiday card from Chandoo.org HQ. The kids school had a cultural festival a month ago and we got this beautiful picture at a photo booth. So much better than timer controlled DSLR on our dining table, I must say.

Thank you

2021 has been a strange year. I know many of you would have faced some form of hardship this year. So I won’t bore you with my details. Instead, let me be thankful for what it has been. Big shoutout to…

You – my dear reader, viewer and supporter of Chandoo.org for being with me all this time and learning.
My family – for supporting & loving me all these years
Microsoft – for creating Excel & Power BI
YouTube – for providing an awesome platform to share my knowledge with you
My teachers – too many to name, but they have always guided & inspired me
My partners – for helping my business and customers – big shout out to Plum Solutions & Global Excel Summit
My staff – Ravindra, Pothi & Chitti for being a part of our team and running our small kingdom smoothly
Our forum members – for lending hand to those who need help
My customers – more than 25,000 of them. Thank you for trusting me.
Blog friends – for sharing ideas and referring people to Chandoo.org
Software providers – for helping me run Chandoo.org – big shout out to WordPress, Elementor, Stripe, E-Junkie, WishlistMember, KnownHost, ConvertKit, Amazon, Gmail and Techsmith

This holidays…

I feel incredibly fortunate to be living in New Zealand where covid is under control. This allows us to spend a bit of time outside our house during the holiday season. We are going a little road trip to Coromandel peninsula on the north-east side of NZ. I will share a post card from the beach when we get there by New Year.

Stay safe and see you all next year with awesome Excel & Power BI goodness. Enjoy your holidays.

The post Merry Christmas & Happy New Year 2021 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Image Lookup – How-to show dynamic picture in a cell [Excel Trick]

Wed, 16 Dec 2020 07:35:05 GMT

Do you ever want to have an image or picture lookup in Excel? Something like below.

In this article, learn how to set up an image lookup using Excel. You can use this to display staff details, product images or machine parts etc.

Video - Dynamic Image Lookups in Excel

If you want to understand the picture lookup technique and see it in action, check out my YouTube video below. For text instructions and images, please read on.

Step 1: Set up your data

To get image lookups, you need to set up your data, ideally in a separate worksheet. The trick is to have one image per cell. Something like this:

Tip: Do not use Tables for storing your data. Instead keep them in a spreadsheet range.

Your images need to fit snugly inside the cell, without touching the boundaries for best results.

Also make sure to turn off gridlines on that spreadsheet tab.

You can do this from View ribbon, as depicted below.

Step 2: Write formula to lookup the image

In a separate worksheet, we will write formula to lookup images. Let’s say we have an input cell with employee name in C5.

We need to get the matching employee picture.

The trick is to write a formula that returns a reference to the image of employee in C5.

You can use either INDEX+MATCH or XLOOKUP.

Let’s use XLOOKUP:

=XLOOKUP(C5, data!$D$3:$D$14, data!$C$3:$C$14)

This will return a reference to the cell with the image of employee in C5.

Of course the result of the formula would be 0, as column C (data!$C$3:$C$14) has no values (just pictures) in it.

Step 3: Insert a named range with the formula

Now that we know what formula to write, go to Formula ribbon and click on Define name button.

Name will be “employee_picture”
Refers to will be our XLOOKUP formula from above

See below illustration to understand how the name can be created.

Step 4: Set up a picture link to show image lookup result

This is the last step folks. Go to the data worksheet where you have all the pictures.

Select any one cell with image and copy it (CTRL+C).
Go back to the results worksheet, right click anywhere and select linked picture from Paste Special options. See below picture to understand.

You will get a picture of the selected employee. But this one is special. It is linked to the cell you copied.
Select the picture and formula bar will show the address of the cell to which it refers.
Now comes the most important step. Go to formula bar and edit the reference. Type =employee_picture and press enter.

Bingo, you have just created a picture lookup.

If you change the input cell and type a different person name, the lookup image will show that person.

Related: Learn more about Picture Links

Tips on using Image Lookup Technique

Image lookup through picture links offers exciting possibilities. Here are some tips & gotchas you want to keep in mind.

The picture link images are just like any other images in Excel. So when you select them, you can use picture format ribbon to apply various formatting options to them. For example, you can crop them to shape or apply shadow effect to them.
Picture links work best if all images are of same size.
While picture links look great on screen, they tend to be grainy when printed.
They work well with dashboards too. For example, You can use picture links to display top 3 products or bottom 5 sales persons in dashboards.
For image lookup scenarios, consider adding a missing person image and use that in XLOOKUP if_not_found parameter. It looks amazing.

Download Picture Lookup Example File

Click here to download sample file with the picture lookup example. It has few different XLOOKUP formulas, an additional image lookup trick to show team members and more. Examine the named ranges to learn how it works.

Got questions about image lookups? Fire away...

Give picture lookups a try and if you face any issues during implementation, please post a comment here so I can help you.

Also, check out these other types of lookups:

Range lookup, find matching range based on input value
Lenient lookup – get the almost matching value
Pricing tier lookup problem
Important lookup functions in Excel

The post Image Lookup – How-to show dynamic picture in a cell [Excel Trick] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

9 Box grid for talent mapping – HR for Excel – Template & Explanation

Tue, 17 Nov 2020 06:58:54 GMT

I learned about a new HR technique for talent mapping during a recent consulting project . It is called 9 box grid. In this page, let me explain what it is and how you can create 9 box grid using Excel.

What is 9 Box Grid?

9 Box grid is a talent mapping tool for Human Resources. This is used to map your staff on two scales – performance and potential. All staff are plotted on 3×3 grid with one side showing performance (from low to high) and another side with potential (low to high).

Here is a sample 9 Box Grid.

Learn more about 9 box grid methodology here [analyticshr.com]

How to create 9 box grid in Excel?

If you have just a handful of staff then it is easier to create 9 box grid by typing the data in 3×3 range in Excel. But if you have a lot of people, then creating a dynamic 9 box grid is helpful. Something like below:

Interactive 9 Box Grid in Excel - Demo

Step by step instructions - Making 9 Box Grid template in Excel

I made a short video explaining the process for creating 9 box grid for talent mapping in excel. Watch it below (or on my YouTube channel) or read on for text instructions.

Step 1: Gather data about performance and potential of your staff

In a blank spreadsheet, gather the data about your staff. At the very least, you want staff name, performance and potential. Feel free to add things like department or team names.

This is how I structured my data:

It is all in a table named talent.

Step 2: Create the talent map 9 box grid visual

You can do it in two ways. If you have Excel 365 and access to dynamic array functions like FILTER(), you can use formulas to generate the 3×3 talent map grid.

Else, you can use Pivot Tables to generate the talent map grid.

Formula approach for Talent Map

Note: this method requires Excel 365 with dynamic array functions.

Set up a 3×3 grid in a blank sheet.

In the top left cell, use the below formula.

=IFERROR(TEXTJOIN(“,”&CHAR(10),TRUE, SORT(FILTER(talent[Candidate],(talent[Performance]=E$8)*(talent[Potential]=$D5)))),””)

The above formula assumes E$8 has the performance rating value and $D5 has potential.

How does this formula work?

We are using FILTER() function to filter down the talent table to all candidates who have given performance (E8) and potential (D5) values

SORT() function sorts these names in alphabetical order.

Finally TEXTJOIN() is used to combine all names to one big text with comma & new line character between names.

Pivot Table approach for Talent Map

Note: this method requires Excel 2013 or above versions

Here is a quick demo of the Pivot Table based 9 box talent map grid.

To create this,

Insert a pivot table from your talent data. During the insert pivot step, enable “add this to data model” option, as depicted below.

Now add “performance” to column labels and “potential” to row labels area of the pivot.
We need to show the names of people instead of count in the values area. This can be done by using measures.
Right click on the table in fields list area and select “Add measure” option.
- Name of the measure: List of names
- Definition: =CONCATENATEX(‘talent mapping’, [Candidate],”, “,[Candidate],asc)

Add this measure to values area of the pivot and your 9 box talent map will be ready.

What is this measure doing?

This measure is concatenating all the staff names that have given performance and potential levels. It is also sorting such names in ascending order.

9 box grid for talent mapping - FREE Excel Template

Click here to download my free 9 box grid talent mapping template. It has sample data of 100 employees and two different talent maps (one with formulas and another with pivot tables). Experiment with it to understand the process or use it for your work.

Work in HR and use Excel? More resources for you...

Here are a few more resources to help you with various HR data analysis and reporting challenges.

General Excel resources to improve your data analysis

The post 9 Box grid for talent mapping – HR for Excel – Template & Explanation appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

6 Must Know Line Chart variations for Data Analysis

Thu, 01 Oct 2020 07:10:53 GMT

Line charts are essential for trend analysis, comparison over time, spotting distribution or volatility in your data. In this page, let me introduce 6 powerful line chart variations to help you with business data analysis. These are,

Line Chart Variations - Video

In the below video, I explain 6 line chart variations with instructions on how to create and work with them. Watch it or read on for the chart details.

Indexed Line Chart

Indexed charts are great for comparing apples with oranges. For example, you can compare sales of two products over a period of time with Indexed Chart, even if the sales numbers are in different ballparks (one sells in millions, other in thousands).

Here is a demo of indexed line chart.

Learn more:

Click here for a tutorial on how to create indexed charts in Excel.

Sparklines

Sparklines are tiny charts that you can fit in a cell. They pack a lot of information and help in visualizing more items.

Here is a sparkline demo:

Learn more:

Understand how to create and work with sparklines.

Spaghetti Line Chart

Often we have too many lines and that can be distracting. Use a spaghetti line chart to drive focus to one of the lines. You can make them interactive so users can pick the noodle they want to taste.

Here is a demo of interactive spaghetti chart.

Learn more:

Watch the line chart variations video [timestamp 3:48]

Smoothed Line Chart

Sometimes our data is too volatile. In such cases, you can smooth it to spot the trend and meaningful changes.

There are various ways to smooth line charts. You can use “Smooth line” option of the chart or apply moving average based smoothing.

Here is a demo of both techniques.

Learn more:

How to calculate moving average in Excel?

Advanced: Smoothed area charts – xmen graphic poster

Forecast Line Chart

Use Excel’s FORECAST.ETS() and other suite of functions to create forecasts from your data and visualize them. You can analyze trend and seasonality of the data with these functions. Forecast charts are helpful in business reporting.

Here is a demo of a forecast line chart.

Learn more:

Introduction to Excel forecasting feature

Line Chart Combinations

No matter how hard you try, sometime a line alone won’t do. In those cases, you can easily combine line charts with other visualizations in Excel. Combination Charts are very easy to create.

Here is a demo of line chart with column combination.

How to create combination charts?

In new versions of Excel (365, 2016+), just go to Insert ribbon and click on Combo chart button (the one that looks like a line column combination).
In earlier versions of Excel, just make a line chart, right click on the line that you want to change to something else and select “Change series chart type” option.

Don't forget the labels ...

You can make regular or any of the variations listed above better by adding sensible labels and chart titles that tell a story.

Here is a demo of special labels on line charts.

Learn more:

Download Line Chart Variations Workbook

Click here to download the Excel file with all these variations. Play with the data or charts to learn more about these 6 line charts.

If you have any questions or suggestions, post them in the comments section.

The post 6 Must Know Line Chart variations for Data Analysis appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Two-level Data Validation [Excel Trick]

Wed, 23 Sep 2020 07:58:57 GMT

Here is a super easy and fun way to create two-level data validation list within Excel. Something like this? In this article, learn how to easily create such two or multi-level drop down lists using Excel.

Two-level data validation drop down - video

If you want a video with all these steps (and some bonus tricks), check it out below. For text explanation with images, just read on.

Download Sample File - Two-level Drop Downs

Click here to download the sample file used in this example. Play with the data or formulas to learn more.

Step 1: Set up your source data

You need data in this format for this two-level drop down technique.

Step 2: Make a pivot table in outline layout

Insert a pivot table from your validation data in outline format with level 1 and level 2 in row labels area. Turn off any grand totals and sub-totals. You will get this.

Step 3: Formulas to get the two-level structure

Now, just refer to pivot table items to generate two-level structure.

We need two formulas. Both are simple to write.

The first one is IF formula

The logic is simple.

If state column is not blank, get that value
Else, if City is also not blank, prefix 5 spaces to city value and return that.
Else return FALSE

The below formula works for me.

=IF(J27<>””,J27,IF(K27<>””,REPT(” “,5)&K27))

My Pivot table is from J27.

Drag this formula down for 50 rows or whatever is the size of your data.

Filter formula to exclude all FALSE values

This next formula will just remove any FALSE values in our 50 cell range. We can use FILTER for this.

=FILTER($O$27:$O$76,$O$27:$O$76<>FALSE)

Note: My IF formulas are in the range O27:O76.

Here is an illustration explaining both of these formulas.

Step 4: Set up the validation rule

We are done people… Just select the cell where you want the two-level data validation drop down to appear and use Data ribbon > validation button.

Set up the validation type as “list” and use the spill range as list. For example, in my workbook, the filter formula is in cell P27, so DV list is $P$27#

Cherry on top: Conditional formatting to flag errors

Our two level validation is already yummy, but if you want a cherry on top, use conditional formatting to flag errors when user picks “level 1” item instead of “level 2” item.

Something like this:

How to set up the conditional formatting rule for this:

In the adjacent cell to the validation cell, write an IF formula that would return or with a message.
Color the cell in Red.
Now apply conditional formatting rule on the cell to turn the color to GREEN if it is

Whipped cream on top: Box drawing shapes + validation

Instead of 5 spaces, you can use box drawing shapes like ∟⊢ to create an impress tree structure. Something like this:

Download Sample File - Two-level Drop Downs

Click here to download the sample file used in this example. Play with the data or formulas to learn more.

More tips on data validation

If you are looking for ways to spice your data validation, you’ve come to right place.

The post Two-level Data Validation [Excel Trick] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Excel formula to convert calendar format to table

Thu, 03 Sep 2020 09:12:50 GMT

Ever have data in a calendar format and just wished you can get it in to tabular format? Something like this:

You can use Excel formulas or Power Query to do this. In this article, let’s review formula based approach with two excellent choices.

Calendar to Table Formulas – Video

If you want to understand the formulas, watch below video or read on.

Using SUMIFS formula

We can use the SUMIFS formula to easily get data for any date, as it works with 2D ranges too.

Let’s say our calendar data is in the range B5:H16,

Use the formula =SUMIFS(B6:H16, B5:H15, “1-Aug-2020”) to get value corresponding to 1-Aug-2020.

Notice the difference between Sum range and Criteria range.

We move the sum range one row down so that we can look at dates above and get the corresponding value from below.

Limitations of SUMIFS approach:

Works only for numbers. If you have text values against each date, then this method will not work.
Can be wrong. Let’s say one of the values is 44044. Which is the number representation of 1-Aug-2020 (as Excel dates are just numbers. More on working with date & time here) Then our SUMIFS result will be wrong.

Learn more about SUMIFS formula.

Using INDEX formula

We can use INDEX formula to access any item in a range by specifying row & column numbers.

For example, =INDEX(A1:D10, 3,2) will return the value in 3rd row & 2nd column of A1:D10, ie B3 value.

So if we can calculate the row & column co-ordinates for a given date from our calendar, we can easily get the answer.

Column number: As our data is in a calendar format, each date will only fall in the column corresponding to the weekday number. We can use =WEEKDAY(input_date, 2) to get the column number, given that our calendar is from Monday to Sunday.

Row number: Once we know the column number, we can extract the entire column and look for given date in that column with MATCH function. This will give us the row number too. The below formula works fine.

=MATCH(input_date, INDEX(calendar_data, , WEEKDAY(input_date, 2)), 0)

Note: the internal INDEX() formula returns entire column as we omitted row parameter. Read up more on this functionality of INDEX formula.

Final formula:

Here is our final formula (with calendar in range B5:H16)

=INDEX($B$5:$H$16,
   MATCH(input_date,INDEX($B$5:$H$16,,WEEKDAY(input_date ,2)),0)+1,
   WEEKDAY(input_date, 2))

Download Example Workbook

Please click here to download the example file with calendar data to table format formulas. Examine the formulas or write your own to understand this technique.

Other ways to convert calendar format data

You can use Power Query to do this job. I highly recommend using PQ, if you deal with calendar style data often. Here is a primer on Power Query.
I have previously written about this problem (with SUMIFS solution) and asked my readers to share their formulas. You can see many other interesting & creative solutions here
VBA: You can use a simple macro to quickly reshape the data. This is a preferred option only if you can’t use Power Query. Here is a tutorial with such macro. The comments on this link also feature many Power Query based transformations.

How would you deal with calendar style data?

For something quick and easy, I will use formulas. For an on-going situation, I will use Power Query.

What about you? How would you deal with calendar style datasets? Please share your thoughts in the comments section.

The post Excel formula to convert calendar format to table appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks

Wed, 26 Aug 2020 08:49:17 GMT

Power Query (Get & Transform data in Excel) is a true game changer. It can simplify and automate various data activities. The key benefits of Power Query are,

In this Power Query Tutorial, learn what it does, how to use it with 4 practical examples, tips & tricks to work with Power Query better.

What is Power Query?

Power Query is a feature of Microsoft Excel and Power BI programs. Power Query is used to,

Set up connections to various data sources
Pre-process data – ex: cleanup, adding columns, filtering, sorting, mashing up
Publish the finalized data to source system – Excel or Power BI

Think of Power Query as your own SQL (querying) tool built in to Excel (or Power BI). Use it to manipulate, mash-up and manage your data.

How does Power Query look?

Power Query window looks almost like Excel workbook. This is why most beginners get confused. The key thing to remember is, Power Query is for connecting, cleaning and manipulating data. You do this by applying steps on your data.

Here is an example of Power Query editor window with 6 key areas highlighted. Click on the image to expand.

Power Query Window - 6 Important Areas

Ribbon: You will see the ribbons on top of Power Query editor UI. There is home ribbon where common query options like column / row adjustments, clean-up and joins are found. The other ribbons are transform, add column and view.
Queries Pane: This is where all the queries (or connections) you have in the workbook are listed. You can select a query to preview its results and work on it.
Data view: You find the preview of current step of selected query here. As you make changes to the data, this preview is updated. This data view looks almost like Excel spreadsheet view.
Query settings: You can adjust query name, see all the applied steps here. We use this to make changes to the query or delete any steps.
Formula bar (optional): The optional formula bar shows corresponding M Language code for current step. You can enable / disable this formula bar from “View ribbon”.
Close & Load button: This button, also shown as “Publish” in Power BI is what we use to close Power Query and return to main application (Excel or Power BI).

How to activate Power Query?

The process for activating or launching Power Query is slightly different in Excel vs. Power BI. Refer to below steps to launch Power Query.

Launching Power Query from Excel

Goto Data ribbon in Excel
Use buttons in “Get & transform data” area to make a new connection.
Or click on buttons in “Queries & Connections” area to refresh or view the existing queries.

Power Query from Power BI

Goto Home ribbon in Power BI
Use buttons in “data” area to make a new connection.
Or click on buttons in “Queries” area to refresh or view the existing queries.

Notes on Power Query availability

Excel: Power Query is available in all versions of Excel from 2013.
Power BI: Power Query is available in all versions of Power BI.

Power Query Tutorial - Video

I made a 1+hour tutorial on Power Query explaining every aspect of it, along with 4 full examples. Please watch it to understand and master this powerful & time-saving technology.

You can watch it below or head over to my YouTube channel.

Power Query as a Mind-map

Power Query is packed with thousands of features to clean, process and manage data. That is why it is tricky to comprehend the overall picture of it. I made a mind-map of Power Query so you can get holistic view of this life saving tool. See it below (click on the image to enlarge).

How to use Power Query?
Four Examples

In this section, I will demonstrate how to use Power Query with four full-length examples. Each example has sample data and completed workbook for you download and follow along.

Includes sample data, completed Excel workbook & Power Query Mind-map PDF

Power Query Example 1:
Load and Clean-up Employee Data

In the first Power Query example, we will look at data for one thousand staff and clean it up. The data is in an Excel file. We will load it to Power Query and perform below clean-up activities. After data is clean, we will publish the dataset to Excel for analysis.

Connect to Employee Data file
Replace missing gender & department values
Remove employees without salary
Extract employee’s country and remove address column
Extract year of join
Publish data to Excel

Whenever there are new employees, we will simply refresh the Power Query connection and it will load new data (after apply clean-up steps) automatically. Just like magic.

The below instructions show how to do this with Excel Power Query. You can apply the same steps in Power BI too.

Step 1: Connect to the data set file from Excel

Sample file for this example - M01.xlsx You need the employee data sample file - M01.xlsx for this example. In the downloaded ZIP file, you will find it in "Datasets" folder.

Go to data ribbon and click on “From File” and select “Excel”. Point to the employee data set.

Here is a quick re-cap of how to connect to data from Power Query.

Step 2: Apply data cleansing steps in Power Query

Once the data is loaded into Power Query, you can quickly apply all the necessary data cleansing steps in there.

The steps will be:

Remove top rows: The file contains 2 rows of header information which is not needed. From “Home ribbon” in Power Query editor, using the “Remove rows” button, remove top 2 rows.
Promote headers: Now that our data is has no extra rows on top, let’s use row number 3 as header. From home ribbon, just tap on the “use first row as headers” button.
Remove blank columns: The file also loads a few blank columns. Just use “Remove columns” button to nix them.
Replace missing values – gender & department columns: Select each column with missing data and either right click or use “replace values” button to find & replace nulls with alternative values.
Remove staff with no salary: This operation is also called “Filtering”. Just use the filter button on salary column and remove any “null” values.

I have illustrated the screen buttons for these 5 data cleansing steps on Power Query UI below. Check it out if you need help.

Power Query Tip: Plan first, clean next

Save a lot of time by planning all your data cleansing steps first. Think about ways in which your data could change in future and build your data cleansing around them.

Step 3: Extracting country from address to new column

So far, all our data cleaning steps are in-place. But now, we must add a new column with the country of employee. You can use “Add column” ribbon of Power Query to do such operations.

For example, to extract “USA” from the address “1 Infinite Loop, Los Angels, CA, USA“, we can use text after delimiter option.

To extract the country, select the address column and use Add Column > Extract > Text After Delimiter option.

Note: In the video, I use a more advanced version of this as our addresses are not so straight forward.

You can use similar approach to add “year” from date of join.

Step 4: Publish data to Excel for analysis & reporting

Once your data is clean and ready, click on “Close & load” button in home ribbon. This will load data to Excel as a table. You can use this table for data analysis or reports.

How to refresh:

Whenever there is new data added to the employee data file, just head over to the Excel file with connection and refresh it (shortcut: Ctrl+Alt+F5 will refresh all connections)

Your file, associated analysis and charts will all be updated.

Power Query Save & Load options – Explained

In Excel: You can load Power Query data in three ways – as a table on spreadsheet, a table to data model or connection only.
Power BI Save & Load: With in Power BI, you can either load a Power Query table or leave it in the query editor. If you do not load a table, you can still have it refreshable for calculation purposes.

for more on this example…

Please refer to the Power Query tutorial video above, timestamp 24:10 onwards.

Power Query Example 2:
Scraping Web Data & Reshaping it

In this example, let’s use Power Query to scrape web data from List of Indian States page on Wikipedia.

On that page, there is a historical census data table (depicted below) and we will connect to it from Power Query. Once we have the data, we will unpivot it to tabular format for easy analysis.

Data for this example - Wikipedia Please use the URL - https://en.wikipedia.org/wiki/List_of_states_in_India_by_past_population for this example.

Step 1: Connect to web data source

From Data ribbon, use the “from web” button. Paste below URL https://en.wikipedia.org/wiki/List_of_states_in_India_by_past_population

and connect.

Power Query will show all the tables on the web page. Select the census table and click on “transform” button.

Step 2: removing unnecessary rows

The wikipedia table has an extra header row and a grand total row. Just remove these with “Remove Rows” button.

Step 3: Unpivoting the data

The census data has state in one column and each census population in one column. We can turn this in to a standard three column table – state, year, population using unpivot option.

Right click on state column
Pick “unpivot other columns” option
Done, your data is now in tabular format.

Power Query Web Connection Tips

Privacy settings: When you make a web connection, PQ will prompt you for access type details. Most web data can be accessed anonymously. But you can also use login access or windows credentials to authenticate requests.
Check frequently: If the source website changes their format or presentation of data, then your Power Query connections will break. It is a good idea to check such connections once in a while to make-sure they are working.
URL parameters: You can use “Advanced” option during connection time to set up URL parameters or variables. This gives you flexibility to access things like page 5 of a result set.

for more on this example…

Please refer to the Power Query tutorial video above – timestamp 47:09 onwards

More Power Query Web Scraping Examples

FIFA worldcup – scores, tables & details – web scraping with Power Query [Beginner]
URL parameter example – weather data to Excel [Beginner]
Using API to access and export video comments from YouTube with Power Query [Advanced]

Power Query Example 3:
Combine data from all files in a folder

You can use “folder” connection option in Power Query to easily consolidate data from all files in a folder.

In the third example, we will take all the project files in the example dataset and combine data to one table. This process is a bit clumsy to explain in text alone. So please watch the video segment (timestamp 58:48) to understand this fully. I am providing a summary of the folder combine technique below.

Data for this example - CSV folder In the download ZIP file, use the Datasets > CSV folder as data source for this example.

Start by making a folder connection (Data ribbon > Get Data > From File > From Folder)
Point to the folder where your files reside
Click on “Combine” button for a simple and quick data combine.
Or choose “Combine & Transform” option for customizing the transformation process.
Power Query will show one of the files and asks you how you want to extract data. Based on your selection, PQ will apply the same logic to all files and combines the data for you.

Here is a quick overview of the folder connection process.

for more on this example…

Please refer to the Power Query tutorial video above – timestamp 1:09:08 onwards

Power Query Example 4:
Joining and Appending Tables

In the last example of Power Query, we will learn how to merge and append tables. These are similar to SQL operations.

Power Query Merge = SQL Join
Append = SQL Union

Data for this example - Students dataset In the download ZIP file, use the "students & courses.xlsx" file for this example.

Appending two tables with Power Query:

To append one table at the end of another table, you must have same columns in both tables. It doesn’t matter if the columns are not in the same order (for ex. table 1 can have student, course and table 2 can have course, student. PQ will append ok).

What if one table has more or less columns?

In that case, Power Query will still merge, by including all columns. The missing values will be shown as null in the final appended table.

To append two tables:

Select either table and view in the preview grid.
Go to Home ribbon > click on “append queries“
Follow the screen prompts and specify the second table name.
Power Query will append second table at the end of first table.

You can also use this to append more than two tables.

Merging (Joining) two tables:

In order to merge or join two tables, you nee a common value in both tables. In the below example, you can merge:

Students & Enrollment on “Student ID“
Course & Enrollment on “Course ID“

Example of common field between tables

Students Table

Student ID
Name
Date of birth
Class
Parent 1 name
Parent 2 name

Course Table

Course ID
Course name
Instructor
Credits

Enrollment Table

Course ID
Student ID

To merge two tables:

Select the source table
Go to Home ribbon and click on “Merge queries“
Follow screen prompts and select common column (field) between both source and target tables.
Specify the join type. Leave the default “left outer join” if you just want matching values in target table when present. Try other kinds of join (merge) if you need.
Power Query will perform the merge and show corresponding rows of target table as a new column.
Expand this column and your merge will be complete.

Download Power Query Examples

Please use below button to download all Power Query examples, sample data and mind-map PDF.

Includes sample data, completed Excel workbook & Power Query Mind-map PDF

Power Query - My Top 5 Tips

Tip #1

Look before leap:

Before you create any complex queries, just jot down all the steps needed and arrange them in logical order. Apply filters & error handling steps first, then add columns or transformations.

Tip #2

Profile your columns

Power Query offers easy and useful column profiling options. Use them to investigate how good your data is and fix problems with a click.

Tip #3

Formula bar is your friend

Enable formula bar from “View” ribbon. This is a one time step. Once you have it on, you can passively learn the Power Query language – M. You can use formula bar to quickly adjust or type new M code.

Tip #4

Right click before you look elsewhere

Many common and useful Power Query operations can be done by right clicking on the columns. This saves time too.

Tip #5

There is more than one way to do things

and that is ok. Just stick with what works in the beginning. Learn and improve your queries over time.

More..?

Got a Power Query tip? Share it with me in the comments section.

Power Query - Recommended Resources

Power Query Books

I recommend the excellent Collect, combine and transform data using Power Query book by Gil Raviv.

Power Query Courses

If you want a comprehensive and hands-on data analysis course with plenty of Power Query examples, check out my Excel School online classes.

Power Query Web Sites

These are my absolute favorite helpful websites that frequently write about Power Query. Please bookmark and enjoy.

Excel Guru blog by Ken Puls – For Power Query tips, practical scenarios and guidance.
Cross Join blog > Power Query pages by Chris Webb – for intermediate to advanced PQ applications.
M language primer (multi-part series) for advanced users by Ben Gribado
Power Query best practices [Microsoft Docs]

More PQ examples on Chandoo.org

Check out below examples for more on Power Query.

The post Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Project Plan – Gantt Chart with drill-down capability [Templates]

Wed, 29 Jul 2020 07:30:00 GMT

Gantt charts are useful for visualizing a project’s timeline and activity flow. In this article, learn how to create an interactive project gantt chart with drill-down capability using Excel. Here is a demo of the gantt chart we will be creating.

Download the Drill-down Gantt Chart Template

Please click here to download the gantt chart template. Just change the input data and click on “Refresh” button from Data ribbon to update the gantt chart.

If you want more project management templates, please click here.

Step by Step Tutorial - Gantt Chart with Drill-down

Please watch below short tutorial to learn how to create an interactive multi-level project gantt chart in Excel. Alternatively, just read on to get the instructions.

If you want to create a similar gantt chart from your data, Please follow below steps.

Step 1: Get your data

You need at least these four columns of data.

Step 2: Make a pivot table from the data

Insert a pivot table from this data. Set it up as shown below. You need,

Slicer on “module”
Activity on row labels
Start date min on values
End date max on values

Step 3: Create a gantt chart empty outline

In a new worksheet, set up gantt chart outline like below.

You need,

4 columns to display activity, start date, end date and duration
another 90 narrow columns to show the project plan. Feel free to adjust the number of columns based on your needs.

Step 4: Making the left side of gantt chart

The left side portion of our project plan is rather simple to make. We just need to refer to Pivot Table values to get first three columns (Activity, Start and Finish).

We can then calculate the duration using =NETWORKDAYS(start, finish)

After the duration is calculated, add conditional formatting > data bars to it, so that we can easily spot activities that take too long to complete.

Step 5: Gantt chart grid (right side portion)

Now that our gantt chart is ready on the left, let’s complete the grid.

Start by calculating the earliest project start date using min formula =MIN(plan[Start date])

Place this formula in the grid top left cell, as shown below.

Calculate remaining 89 dates by adding +1 working day. Use =WORKDAY(previous date, 1) formula for this.

This will give us a bunch of dates.

Use the next two rows to show month & day portion of this date by referring to the date calculation row. As the cells are too small, merge 2 or 3 of them and show the values.

Now that all the dates are ready, let’s figure out the logic for making gantt chart view.

As shown above, we need a rule to highlight any cell if the date in top row falls between start and finish dates for the corresponding project activity.

To do this, select the entire grid of 100 rows x 90 columns and apply a new conditional formatting rule.

Use “formula” type rule and apply this formula.

=MEDIAN($C6, $D6, M$3) = M$3

Adjust cell references based on your gantt chart setup.

Apply necessary formatting and your gantt chart will be ready.

Step 6: Move the slicer to the gantt chart worksheet

This is the last and easiest step.

Just cut and paste the slicer near the gantt chart. Your interactive chart is ready.

Bells & whistles:

You can add a conditional formatting rule to highlight current date
Another rule to highlight alternative rows (zebra-shading)
Adjust the conditional formatting rule to show completed activities in a different color.

How to update the Gantt Chart?

When ever you have new data, simply update the input data worksheet. Then refresh pivot tables (shortcut: Alt+Ctrl+F5). Your Gantt chart will be updated too.

Download the Drill-down Gantt Chart Template

Please click here to download the gantt chart template. Just change the input data and click on “Refresh” button from Data ribbon to update the gantt chart.

If you want more project management templates, please click here.

Questions or Suggestions?

Got some questions or issues when using this template? Have a suggestion for this Gantt Chart? Please post them in the comments section.

Also check out:

The post Project Plan – Gantt Chart with drill-down capability [Templates] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

How to make stream graphs in Excel?

Thu, 16 Jul 2020 09:00:09 GMT

Stream graph or stream plot is a variation of area chart that looks like a stream. Here is a quick demo of a stream chart. Learn how to create such a graph in this page.

Background & Inspiration

Stream graphs have been around for many years. But this particular graph, showing the appearance of X-men characters is originally designed by Cédric Scherer. I saw his tweet sometime back and immediately wanted to recreate this in Excel.

So I went to the github project page where Cedric shares his R code, datasets and detail on the construction for all his visuals. I grabbed a copy of the dataset and used it to make the interactive stream graph in Excel.

Big thanks to Cedric for the inspiration.

Download the Stream Chart Workbook

Please click here to download the Stream Chart: X-Men appearances workbook. Play with it to enjoy. Examine the calculations, worksheet setup and chart formatting options to learn more.

How to make a stream graph - 4 steps

As you can guess, there is no “Stream graph” option in insert ribbon on Excel. So we will need to make it by mixing a few bits and bobs. For the first part of the tutorial, let’s focus on creating a stream plot in Excel. The process involves 4 simple steps.

Make a regular area chart
Float the areas with a dummy series at bottom
Smoothen the areas
Format the chart

Let’s start with sample data as depicted below.

Step 1 - Make regular area chart

Select the data and insert a stacked area chart. We get this.

Step 2 - Float the areas with dummy series at bottom

To make our area chart look like a stream, we need to first lift the areas. To do this, we need to add a dummy series at the bottom.

But what would the value of this dummy series?

To make it look like a stream, each point of the area chart needs to move down or up around a center line.
We can do this by taking a big enough number (say 100) and subtracting half the height of total area at each point.
To make it look natural, we can add a small random number to this.

Here is how our dummy float series will look:

And this is how our area chart will look once you add the dummy series at the bottom.

Step 3 - Smoothen the areas

You must be thinking… “Hey, where is the stream… ?”

We are getting there. Our area chart has all sorts of sharp lines. We just need to smoothen it out. I recommend 60 grit sandpaper.

Just kidding. No need to sand the edges. We just use formulas to smoothen the data. Time for some concepts.

How to smoothen / curvify data in Excel?

If you are making a line graph, you can use the ever helpful “smooth line” option to smoothen it. Unfortunately, we need to use area charts for the stream effect. So we are out of luck and need to learn how to smoothen or curvify our data.

You can use regression to smoothen the data, but it tends to be too complex.

The easy option: use moving average.

Take your data and apply moving average with a window size of 5 (try few different options and pick the one that gives good looking curve).

Here is a tip on how to calculate moving average in Excel.

Here is the moving averaged data. Remember to include the dummy series in moving average calculations.

If you create an area chart from this data, suddenly those areas don’t look so sharp anymore. Our stream is almost ready!!!

Step 4 - Format the chart

This last step is real simple. Just format the chart by making dummy series transparent. Remove any unnecessary chart elements and you have a nice stream going.

Back to X-Men Appearances Stream Chart

Now that you know how to make a stream appear, let me unravel the rest of this beautiful x-men appearances chart.

Data for the visualization:

The data for this came from Github project page (link). It is in a convenient CSV format. I used Power Query to connect to the file and add an extra column to extract mutant character names.

Interactivity – Option buttons:

The interactivity in the stream graph is done with 4 option buttons, icons, text & conditional formatting. See below illustration to understand how they all create the illusion.

Stream chart edges – lines with smoothing option:

To get the crisp stream effect, I have added two lines, one at bottom and one on top of the stream. I made these lines smooth lines and colored them in white.

Axis labels are data labels on a hidden line:

I added another line series on top. Used that to make axis labels for important issue #s and hidden the line. We get these beauties.

Error bars for gridlines:

As regular gridlines can be too grainy, I removed them and used error bar technique to draw a line from bottom to top of the stream.

Legend is shapes + typed text:

The legend is almost manual. I just created a bunch of rectangles and typed the text in them. I used gradient fill technique to get two colors in the same box.

How did the stream tapered nicely?

That is for you to figure out. Use the download file to learn how that is done.

Stream Chart in Excel - Video

I made a video explaining the whole process. It has many additional charting tips too. Watch it below or on my YouTube channel.

Download Stream Chart Excel File

Just in case, you missed the link above…

Please click here to download the Stream Chart: X-Men appearances workbook. Play with it to enjoy. Examine the calculations, worksheet setup and chart formatting options to learn more.

Advanced Excel Charting Ideas & Inspiration

If you are looking for creative, fun and interesting ways to present data, you’ve come to the right place. Please click on below images to learn more.

The post How to make stream graphs in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

These Pivot Table tricks massively save your time

Mon, 06 Jul 2020 08:30:00 GMT

Pivot tables are powerful. Use these 6 tricks to save time when working with them.

1 - Double click to see details

Ever wondered what numbers add up to the total in a pivot table value area? Simply double click to investigate. Excel will show all underlying values.

Extra tip: Don’t forget to delete such detail worksheets as they can create duplicate data.

2 - Format value fields quickly

The default value field formatting is useless. Often, we need to change the values to currency or accounting formats. You can do this quickly by right clicking on the values instead of going to Value Field Settings > Number Formats route.

See this image to understand the process.

3 - Include filtered items in subtotals

Let’s say you have a pivot table with Sales by product & region. You filter out some products. The grand totals change. This can be inaccurate.

If you want to to include filtered items in the totals you can use the option in “Subtotals” menu in ribbon. Oh wait a sec, that is grayed out

Turns out you can ungray it. All you need is to use data model. Click on “Add to data model” in the insert pivot screen you are able to use this feature.

4 - Consistent pivot charts with a simple trick

Let’s say you have a pivot chart linked to a slicer showing sales by product in selected region. As you don’t sell all products in all regions, the chart looks jumpy with inconsistent axis.

Something like this:

You can make the axis consistent by formatting the row label to show “items with no data”. To do this, select the row labels area of the pivot, right click and go to “Field settings”. Now from display area, enable “Items with no data” option.

Your chart looks much better.

5 - Repeat row labels

Tabular form is my favorite layout for pivot. It looks clear and easy on eyes.

Do you know that you can enable “Repeat item labels” option to make the tabular layout even better.

Bonus tip: When you repeat item labels, you can use Pivot Tables in other formulas (like SUMIFS) easily.

6 - Multiple pages with Report Filters

You got a handy little pivot report that details the performance of a region. Now you want to make similar reports for rest of the 16 regions too.

You can use Report Filters to quickly create all versions of this report in one click. Here is a demo of how report filters work.

Click here for more tips & info on Report Filters.

Pivot Table Tricks - Video

If you want to view all these tricks + my new haircut, check out below video. You can also watch this on my YouTube channel here.

More Pivot Table goodness

Work with data often? Then learn Pivot tables you must.

Check out below pages to pick up valuable pivoting skills.

Got some tricks? Teach me...

You can teach an old dog new tricks. If you know a cool & useful Pivot Table trick, post it in comments. I am eager to learn from you.

The post These Pivot Table tricks massively save your time appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

50% done – mid year updates for 2020

Thu, 02 Jul 2020 06:56:19 GMT

Hello folks. 50% of 2020 is behind us. Let me share a few updates with you all. First check out this awesome double rainbow outside our house on 30th June.

MVP

I am thrilled and humbled to tell you that I have been re-awarded Microsoft MVP for 2020 year. This is my 12th year in the MVP program and it is all possible only because of you and your continued support to Chandoo.org. Thank you.

Lock down and back to normal…

Just like many other countries around the world, New Zealand entered lock down during March 2020. These restrictions gradually eased in May & June 2020 and we are finally back to normal life here. Thanks to excellent disease management by the local authorities & government here, the lock down was sharp, quick and effective.

I hope you are staying safe and healthy.

Power BI Play Date – July 2nd week

The next batch of Power BI Play Date is coming soon. I have originally planned to run this in March 2020, but kept moving the goal posts. But the course is finally launching in 2nd week of July. Check out the program details here and add your name to waiting list if you are keen.

Remote training for corporates

Are you looking to run Power BI or Advanced Excel training to your team? I am now available for 100% remote training. Find out more details here and get in touch if you want to discuss.

Mangoes…

As you know Chandoo.org family moved from India to New Zealand in 2016. One of the most difficult parts of the move is giving up mangoes. While you can purchase mangoes in the supermarkets here, the sweet & pulpy Indian varieties like banganapally, alfonso or kesar are hard to to get.

With lock downs & restrictions all over the world, things looked even more ominous.

Well, I am glad to tell you that the gastronomical cravings have been sated. We bought two boxes of banganapally mangoes sometime ago and micro-dosing on them everyday. Yummy!!!

Here is me sniffing a mango before carefully stuffing it back in the box

And oh yea, I have a beard now.

That is all for now. We will revert back to usual doses of Excel & Power BI goodness from Monday. Enjoy your weekend.

The post 50% done – mid year updates for 2020 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Top 10 Excel formulas for IT people

Thu, 18 Jun 2020 07:40:00 GMT

Are you AND(“In IT”, “Use Excel”)? Then this article is for you. Here is my list of top 10 Excel formulas for people in IT. As software miners use Excel differently than other folks, this article highlights important use cases for you. These examples will particularly help project managers, IT analysts, testing people and business analysts.

Don’t forget to download the example workbook.

Video - Top 10 Excel formulas for IT people

If you don’t want to read the article, watch this video for my recommended top 10 Excel formulas for IT people.

Download Example Workbook

Click here to download the 10 Excel formulas workbook. Use the sample data and completed formulas to learn all of these.

Sample data for these formulas

For most situations in these top 10 examples, I will be using the below two column dataset (named testing). This shows number of test cases completed on daily basis for the month of May 2020.

1. Count values between dates

Let’s say you want to count how many test cases were completed between dates 6-May-2020 and 16-May-2020.

You can use below formula:

=SUMIFS(testing[Test cases done],

testing[Date],”>=6-May-2020″,testing[Date],”<=16-May-2020″)

Know more about SUMIFS function.

To see all testcase data for the given date range in cells J4 & J5, you can use FILTER function.

=FILTER(testing, (testing[Date]>=J4)*(testing[Date]<=J5))

Know more about FILTER function.

2. Lookup a value

Given the test cases data, you want to know how many cases were done on a given date – 18-May-2020?

You can use either XLOOKUP or VLOOKUP for this. I recommend using XLOOKUP if you are running Excel 365.

Refer to sample formulas below.

=XLOOKUP(“18-May-2020”,testing[Date],testing[Test cases done])

=VLOOKUP((“18-May-2020”,testing,2,FALSE)

3. Gap between two dates

Dates & time values are used often in IT situations (project planning, estimation etc.) To calculate the gap between two dates, simply subtract one from another. Like this:

=date2-date1

For more on date related calculations, refer to this excellent guide.

4. Working days between two dates

What if you want to calculate the FTE (full time equivalent) for a project module. You can use NETWORKDAYS formula to find out the number of working days between two dates and multiply that with headcount to get FTE required.

Something like this:

Number of working days =NETWORKDAYS(date1, date2)

Number of working days with Friday, Saturday weekend =NETWORKDAYS.INTL(date1, date2, 7)

Working days for 4 day workweek (MTWT) =NETWORKDAYS.INTL(date1, date2,”0000111″)

The pattern 0000111 refers to Monday to Thursday as working days (0) and Friday to Sunday as weekend (1).

5. N^th smallest item

We can use MIN() to find the smallest item in any data (and MAX() for largest). But what if you want to find the 2nd smallest item or 4th largest one?

You can use SMALL() and LARGE() formulas for this purpose. See below examples:

2nd smallest number of cases in any day =SMALL(testing[Test cases done],2)

3rd highest number of cases in any day

=LARGE(testing[Test cases done],3)

Which date had 2nd smallest number of cases completed?

=XLOOKUP(SMALL(testing[Test cases done],2),
testing[Test cases done],testing[Date])

6. One week moving average

Moving averages help with understanding the trend. Let’s say you want to know what is the 7 day moving average of test cases done? You can use AVERAGE formula with relative references, as illustrated below.

7. Extract portions from text

You can use Excel’s flash fill to extract portions of text on one time basis. If you want to make it more dynamic then you must learn the text formulas. There are many, but start with these 4.

FIND – to find the position of one text in another
LEFT – to get part of text from left
RIGHT – to get part of text from right
MID – to get text from the middle

Here is an example to extract user name from email address in cell J4 (john.doe@acme.com)

=LEFT(J4, FIND(“@”, J4)-1)

8. Combine text values

If you want to combine a bunch of values but include a separator in between, use TEXTJOIN function. Here is an example to combine email addresses in cells J4:J8

=TEXTJOIN(“, “,,J4:J8)

For more on TEXTJOIN refer to this page.

9. Generate random data

Excel has many useful functions to create random data. If you just want a random number between two values, use the RANDBETWEEN() function. To generate a bunch of random values, use the newly introduced RANDARRAY() function. See these examples:

10 Random whole numbers between 1 & 100:

=RANDARRAY(10,,1,100,TRUE)

A random phone number:

=RANDBETWEEN(1111111111, 9999999999)

8 random dates in year 2020:

=RANDARRAY(8,,”1-JAN-2020″,”31-DEC-2020″,TRUE)

Other examples of random data in Excel:

10. Avoid errors

Just as you want to have bug free code, your Excel files should also be devoid of errors. You can do this in below ways:

Use if not found option with functions like XLOOKUP, FILTER, XMATCH etc.
Use IFERROR to print alternative message when your formula has error
Check errors with ISERROR function

Download Example Workbook

Click here to download the 10 Excel formulas workbook. Use the sample data and completed formulas to learn all of these.

Something else?

Do you want to learn more? Please share your questions or suggestions in the comments box. I will create a post or video to help you.

Also check out the Top 10 formulas for data analysis page for few more ideas.

The post Top 10 Excel formulas for IT people appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Lookup last non blank value – Excel homework

Fri, 12 Jun 2020 08:18:00 GMT

I have a fun Excel lookup challenge for you. You have data as shown below and want to find the last non blank value for a given account number. For example, for acct number 2015, the answer would be Freedom.

How would you solve this? Post your formulas, code, steps or incantations in the comments box. Go.

Fine print

Assume your data is in range A4:G13 with A4:A13 having the Account number and other columns containing some details.
The lookup value can be hardcoded or assumed be to in cell K3.
Click here for sample data file. This file also has some hints and 3 possible solutions.
You can use any Excel formula or Power Query or VBA based solution.
Post your answers in comments.

Happy solving.

If you want more Excel challenges & homework problems, click here.

PS: Thanks Barbara for emailing me this question.

PPS: If you are not sure how to solve this, come back on Tuesday to see a video with my solutions.

The post Lookup last non blank value – Excel homework appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Excel TEXTJOIN Function – What is it, how to use it & 3 advanced examples

Tue, 09 Jun 2020 07:26:00 GMT

Use TEXTJOIN function to combine text values with optional delimiter. It is better than CONCATENATE because you can pass a range instead of individual cells and you can ignore empty cells too. Here is a sample use of TEXTJOIN Excel function.

Why TEXTJOIN? Why not CONCATENATE or & operator?

TEXTJOIN is easy

To combine values in A1:A5 with CONCATENATE, we have to write =CONCATENATE(A1, A2, A3, A4, A5) or A1&A2&A3&A4&A5

With TEXTJOIN, you can simply say =TEXTJOIN(,,A1:A5)

Add delimiters

You can comma separate or add other delimiters when combining text with TEXTJOIN. While you can do the same with CONCATENATE, it becomes doubly annoying.

=TEXTJOIN(“, “,,A1:A5) gives you a comma separated text of values in A1:A5

3 Advanced TEXTJOIN uses

Here are three advanced examples of TEXTJOIN.

1. Combine values that meet a criteria

Let’s say you want to combine all player names for kickers team as shown aside. You can use TEXTJOIN with IF formula to do this.

=TEXTJOIN(“, “,TRUE,IF($C$36:$C$63=”kickers”,$B$36:$B$63,””))

The inner IF function returns an array of team members or blank depending on whether they are in kickers or not.

TEXTJOIN then ignores all blank values and returns comma seperated text.

Same formula with FILTER()

You can also use the newly introduced dynamic array function FILTER to do this job.

=TEXTJOIN(“, “,,FILTER(B36:B63,C36:C63=”kickers”))

Learn more about Excel Dynamic Array functions.

2. Reverse a text

You can use MID function to split a text value in to an array of characters and then reverse them with TEXTJOIN.

For example, below formula reverses “Chandoo”

=TEXTJOIN(,,MID(“Chandoo”,21-ROW($A$1:$A$20),1))

It works up to 20 letters.

How does it work?

MID(“Chandoo”,21-ROW($A$1:$A$20),1) portion returns an array of 20 items {“”;””;””;””;””;””;””;””;””;””;””;””;””;”o”;”o”;”d”;”n”;”a”;”h”;”C”}

This is then combined by TEXTJOIN to return 00dnahC

3. Substitution cipher with TEXTJOIN

Want to communicate in a secret code with your colleague? You can use substitution cipher to map each letter with another letter of alphabet.

We can use either XLOOKUP or VLOOKUP with TEXTJOIN to encrypt text values like this.

Imagine you have a mapping table as shown aside.

To convert a message in cell H92, use this formula

=TEXTJOIN(,,XLOOKUP(MID(H92,ROW($A$1:$A$20),1),subs[Normal], subs[Code],””))

How does it work?

Using the MID() function, we split the text in H92 into an array of 20 values, one per character.

We then pass these letters to XLOOKUP to find matching [Code] from the subs table.

All these values are finally combined by TEXTJOIN to generated our coded message.

What the function - TEXTJOIN in Excel video

I recently started a series of videos on my Youtube channel to demystify Excel functions. This series is named What the function. Watch below WTF tutorial on TEXTJOIN to learn how to use it.

Click here for full WTF playlist.

Download TEXTJOIN Example Workbook

Click here to download the TEXTJOIN example file. Use the sample data and formulas to understand TEXTJOIN function better. Play with the data to create something cool on your own and post your TEXTJOIN ideas in the comments.

TEXTJOIN in use - Excel Risk Map

If you want to get some inspiration on how TEXTJOIN can be a force of good, see this Excel risk map. It uses TEXTJOIN to show risks in a matrix format.

The post Excel TEXTJOIN Function – What is it, how to use it & 3 advanced examples appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

How to make a variance chart in Power BI? [Easy & Clean]

Thu, 04 Jun 2020 09:20:00 GMT

Power BI is great for visualizing and interacting with your data. In this article, let me share a technique for creating variance chart in Power BI. Variance charts are perfect for visualizing performance by comparing Plan vs. Actual or Budget vs. Actual data.

This is what we are going to build.

What you need to build a variance visual in Power BI?

You need a few basic measures.

Actual values
Variance
Variance %

These measure definition will depend on what you are visualizing. So I won’t go in to that detail, but 99% of time, they will simple aggregations such as SUM or AVERAGE.

Power BI Variance charts – Video

If you want a quick walk thru of the variance chart construction, check out below video. Read on for text instructions.

Variance chart in Power BI – Step by step instructions

Step 1: Make a bar / column chart with Actual values

This is easy. Just make a bar (or column) visual with actual values. It will look like this.

Step 2: Add a similar chart for variance and place it next to the actual chart

I would just copy and paste the visual and change the measure. We will end up with this.

Step 3: Apply conditional formatting rules to color variance bars

Select variance visual, go to Format > Data colors and click on fx button to apply conditional formatting rules. You want green color for positive values and red for negative values (assuming positive variance is good, else flip your rules).

The rule setup looks like this:

At this stage, our variance chart looks like this. It has a teeny tiny problem. The sort order is not in sync between both visuals. ?

Step 4: Synchronize sort order trick

You can sort both visuals in alphabetical order, but that would make the charts less useful. So let’s stick with the descending order of actual values.

Just add “Actual Sales” measure to the tooltip area of variance bar chart. Now you can sort that chart on “Actual Sales” too. Pretty neat eh?

Step 5: Tidy up the formatting & group visuals

This is the last step. Just clean up the formatting of both charts. These are the steps:

For actual values bar chart:
- Color the bars in something dull
- Remove Y & X axis titles
- Remove visual title
Variance bar chart:
- Adjust Y axis “inner padding” . This makes the bars thin. See right.
- Remove Y axis – title & all
- Knock off X axis titles and visual title
Make sure both visuals are in same height and top-aligned. Adjust the width of variance bar chart if not done.

Now just select both visuals (hold CTRL key to multi-select) and group them. This way you can move or resize them together.

That is all. Your variance chart is now ready. Enjoy it.

You can enhance it by adding a table that shows detail, something like this:

Download Variance Chart sample workbook

If you are not sure about the instructions or just want a ready to use example, here is the Power BI workbook. This file also features a tool-tip that shows performance at sub category level.

New to Power BI? Check out this excellent getting started guide.

Want something like this in Excel? See this budget vs. actual chart.

Note: Thanks to recent webinar Andrej & Reza for the inspiration to this article.

The post How to make a variance chart in Power BI? [Easy & Clean] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

How to make an Interactive Chart Slider Thingy

Tue, 02 Jun 2020 09:10:34 GMT

Ok, I will be honest. I have no idea what to call it. May be Chart Cover Flow? But Interactive Chart Slider Thingy sounds so better. So let’s go with it. Here it is:

In this article, let me explain the process for creating such an interactive Excel chart display.

What you need?

You need a few charts, some time and understanding of simple Excel formulas.

Got them all? Let’s roll.

Video – Interactive Excel Chart Slider

Although the instructions for this are not technical, You will benefit from watching this video before reading further.

Step by step tutorial – Interactive Chart Slider Thingy

Step 1: Position your charts in a range, one chart per cell

Select a range of cells and resize them so that each cell can accommodate one chart. Place charts neatly, one per cell. Name this range as charts. Something like this:

Note: While this example shows how to do the ICST (Interactive Chart Slider Thingy, keep up) with 7 charts, you can apply this idea to any number of charts.

Step 2: Set up scrollbar form control

Exactly what it says on the box. Using Developer Ribbon > Insert, add a scorllbar form control to your worksheet.

Right click on the control to set its limits from 1 to 7 and link it to a cell.

Tip: If you can’t tell your developer ribbon from oven mitten, check out this excellent guide on Excel form controls.

Step 3: Calculate neighbors

For our ICST to work, we need to show selected chart in the middle and the neighbors to left & right. We can calculate the neighbor numbers from a given chart number n using simple formulas. I am leaving it to your imagination.

If you need some help, here is a demo of how the neighbor numbers:

Step 4: Create 7 named ranges, one per chart cell

Now that we have neighbor numbers and charts range (from step 1), we can create 7 named ranges, one per chart.

mid.chart: refers to the chart that goes in the middle. Use =INDEX(charts, linked_cell_for_scrollbar)
left1, left2, left3: are the the neighbors to left. Just use =INDEX(charts, neighbor_number) pattern.
right1, right2, right3: same as left

Step 5: Time for some linked pictures

What is that you are saying?
Are we there yet?
Yes, we are sweetie. Almost there.

Go back to your charts range and pick any cell. Copy it.

Go to the place where you want your final visual. Right click and select Paste Special > Linked Picture.

Tip: Here is a quick tutorial on Linked Pictures.

After you pasted any cell as linked picture,

Select the picture.
Go to formula bar and type =mid.chart the name of the selected chart
Copy this picture and paste again
Change reference to =left1
Continue 5 more times and use the names left2, left3, right1, right2, right3

Now, we have 7 pictures, one per each chart. They are all linked to the scrollbar, so if you slide it, you will see the images change.

Step 6: Format the picture links

This is the final step. We just need to format the picture links to mimic album coverflow look. The key steps are,

Shadow effect to make the chart look floating
Reflection effect
3D rotation for non-active charts
Send to back to move the non-active charts behind

See this illustration to understand typical settings for one of the picture links.

That is all. Your interactive chart slider thingy is ready now.

Download Interactive Chart Slider

Click here to download Excel file with full interactive chart slider example. Play with it to learn more. Customize the file or copy the ideas to your work.

If you end up creating something cool or funny, please share it in the comments so we all can learn.

More wacky and fun Excel charts

If you want to spice up your Excel chart life, you’ve come to the right place. Dim the lights, turn on soothing jazz and enjoy these goodies.

Grammy Bump Chart

Explores the bump in album sales after winning prestigious Grammy award.

Flu trends chart

Visually explore and compare flu trends between years & states. Inspired from Google flu trends.

Tour de France – Distance vs. Pace

A radial chart to show distance vs. pace in Tour de France.

The post How to make an Interactive Chart Slider Thingy appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

How to export YouTube video comments to Excel file? – Free template + Power Query case study

Thu, 28 May 2020 07:53:35 GMT

This week, I am running a contest on YouTube. One of the criteria for picking winners is that they must comment on my video. So far, I got more than 200 comments. To make my job easier, I want to export video comments to Excel file. Turns out this is easily done once you have a Google developer API key. In this article, let me explain the process how to export Youtube video comments to Excel table.

You can use this idea to export other data for a video too (views, likes vs. dislikes, listing of all videos in a channel etc.)

What you need

You need,

The video id
Your Google Developer API key – get it from Dev console
optional – pageToken if you want to see comments beyond first 100

The YouTube comments export template

If you don’t care about the bits & bolts of how this works and just want to use the template, please download it here.

Input these values:

Video ID
Google API key
optional Page Token

Go to comments tab and press ALT+F5 to refresh it.

This template will fetch first 100 comments of any video. If you want more comments:

Fetch the first 100 comments
Make a backup of those comments in a new file
Note the nextpageToken in the result table column G
Use this value as page token for input parameters
Refresh the query and copy the values to backup file
Repeat steps 3 to 5 for every 100 comments

How to get YouTube comments with Power Query?

If you want to know the behind scenes for this little extractor, read on.

The URL pattern for YouTube Data v3 API

You can use a simple GET request (ie if you paste this URL in a browser or web extract tools like Power Query or Excel’s WEBSERVICE, it will work) to access YouTube Data API (ref).

To see all comment threads in a video, you use below URL pattern.

https://www.googleapis.com/youtube/v3/commentThreads?maxResults=100&videoId=VID_ID&key=[YOUR_API_KEY]

Notice the red bits. They are the parameters you need.

How to get the results in Power Query?

Once you have the necessary parameters in Power Query – either by loading an Excel table or setting them up as parameters in PQ,

Construct the URL by combining parameters with the pattern shown above.

From the URL in a column (named URL), use below code to create a custom column to fetch the data.

=Json.Document(Web.Contents([URL]))

This will fetch a Json document with the entire comment thread tree for first 100 comments.

You want to expand it a few times. This path should work:

Items > Top level comment > Snippet

This will add a bunch of columns with all sorts of detail.

Keep below columns:

authorDisplayName
authorChannelUrl
textDisplay
textOriginal
publishedAt
nextPageToken

When done, close & load to see these comments in Excel or Power BI as a table.

How to paginate?

Unfortunately, the YouTube Data API is not paginated with sequential numbers. Instead you must provide a pageToken with the request to get next 100 comments.

You can build an accumulating function that first gets all the pageTokens with something like List.Accumulate.

Once you have such a list, you can then just call the URL for all tokens.

This is just too much hassle for a few hundred comments, but makes perfect sense when you have thousands of comments.

An easier option is to just back-up the data (either manually or thru VBA) and refreshing the query with next page token.

Errors & issues

Privacy warning or error:

This is the first one you might encounter. Power Query doesn’t like mixing cell values, parameters etc with web queries. You can go to Query editor and adjust query options from File menu to ignore all privacy warnings.

You may need to rebuild the query to get the results after this step.

API issues:

It is a good idea to test the URL from your Google developer console. Make sure you can actually see some results there before trying it with Power Query.

Please also refer to YouTube API documentation for help on the parameters and output formats.

All other errors:

I would start by checking my internet connection and errors with either video ID or API key or page token. If all else fails, leave a comment and I will share what I know.

Have you used YouTube API from Excel or Power BI?

Have you used the Google APIs to build something cool in Excel or Power BI? Please share your experiments in the comment section.

Other useful Excel templates:

The post How to export YouTube video comments to Excel file? – Free template + Power Query case study appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Celebrating 50k Subscribers on YouTube + Give away

Mon, 25 May 2020 08:49:53 GMT

Hiya folks… Got an exciting news to share with you all. Over the weekend, my YouTube channel hit 50,000 subscriber milestone.

Thank you so much for making me a part of your journey to awesomeness.

I want to celebrate this occasion with 3 things:

Special message from cute guests
5x Amazon gift card give away
5x Excel tricks

Check out all the details in below video (or watch it on my YouTube channel)

50k subs – give away contest rules

To participate, you must do all three things listed below:

Subscribe to my YouTube channel (here)
Like any of the videos on my channel
Leave a comment on the give away video (here)

Important dates:

Contest opens – today (25th of May 2020)
Contest closes – Monday, 1 June 2020 – end of day, Pacific time

Thank you

Thank you so much once again for being a part of Chandoo.org community. You motivate and inspire me to learn and share. Thank you.

The post Celebrating 50k Subscribers on YouTube + Give away appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Highlight due dates in Excel – Show items due, overdue and completed in different colors

Mon, 18 May 2020 04:20:00 GMT

Congratulations to you if your job does not involve dead lines. For the rest of us, deadlines are the sole motivation for working (barring free internet & the coffee machine in 2nd floor, of course). So today, lets talk about a very familiar problem.

How to highlight overdue items in Excel?

The item can be an invoice, a to do activity, a project or anything. Here is an example of overdue, upcoming activities highlighted.

highlight-overdue-items-in-excel

The problem – Highlight due dates in Excel

Lets say you work at Awesome inc. and you have list of to-do items as shown below.

sample-data-overdue-items

And your problem is,

Highlight items & due dates, subject to these conditions
And of course start working on the items that are due

The Solution – Conditional Formatting

As you can guess, highlighting the due items is easier than actually doing them. First lets look at the solution and then learn why it works.

Lets assume that,

The data is in the range – B6:D15, with Items (column B), Due date (C) and Completed?(D)

How to apply conditional formatting rules

We need to apply 3 rules. Follow below steps:

Highlight overdue items:

conditional-formatting-rule-for-overdue-items

Select the entire range (B6:D15) and from home ribbon select conditional formatting
Click on New rule
Select the rule type as “use a formula…”
Write =AND($C6<=TODAY(),$D6<>”Yes”)
And set fill color to red & font color to white.

Highlight upcoming items:

conditional-formatting-rule-for-upcoming-tasks

Add one more “use a formula…” rule
Write =AND(MEDIAN(TODAY()+1,$C6,TODAY()+7)=$C6,$D6<>”Yes”)
And set fill color to green.

Completed items rule:

Add another “use a formula…” rule
Now write =$D6=”Yes”
And set font color to dull gray from formatting button.

Now, the items will be highlighted based on the current date (TODAY) and change colors as you make progress.

Why does it work? – Explanation

At this point you may have 2 burning questions.

Why does this work?
How the heck am I supposed to ship 100 units of smile.

Lets talk about the solution & understand why it works.

Understanding the highlighting conditions

We have 3 conditions in our highlight table (shown above).

If done show in dull gray color
Not done & due in next 7 days show in orange color fill.
If not done & already due show in red fill, white color

Rule for completed items:

The first condition is easy to check. We just see if a todo item is completed and then highlight the whole row dull gray color. So we write =$D6=”Yes” as the condition. We use $D6 (not D6) because we want Excel to look at column D (completed?) even when we are highlighting other columns (B – Item, C – Due date).

If not done & due in next week:

This is tricky. We need to check,

If completed is not yes

AND

If due date is with in next week

So we start with an AND formula. We write =AND($D6<>”Yes”

Then to check if due date is in next week, we use MEDIAN formula, like this MEDIAN(TODAY()+1,$C6,TODAY()+7)

So the condition becomes =AND(MEDIAN(TODAY()+1,$C6,TODAY()+7)=$C6,$D6<>”Yes”)

If already due:

This is another simple AND formula =AND($C6<=TODAY(),$D6<>”Yes”)

Remember:

We need to use $D6 & $C6 (instead of D6, C6) because we want Excel to check Completed & Due date columns. By removing the $ Excel will check relative columns and the conditions would not work!

More: Using relative vs. absolute references in Excel formulas

Now that we understand how this works, give me a big smile. And repeat that 99 more times & you know how to ship 100 smiles

Highlight overdue items – Video

If you are still confused about the conditional formatting rules for highlighting overdue items, check out this video. Watch it below or see it on my YouTube channel.

Download Example File

Click here to download example file. Break it apart, play with it to understand the whole highlight if due thing.

Note: I use random formulas to generate due dates & completed values. Press F9 to get fresh set of dates. Start typing your own values to remove formulas.

How do you handle dead-lines?

Do you use conditional formatting to see which items are due? I use conditional formatting for this all the time. What techniques you use? Is your dead-line criteria very different than shown above? Please share your tips & ideas with us using comments. I would love to learn from you.

Using Conditional Formatting & Dates – More Examples

Here are a few useful articles if you use Excel to track to do items & reminders.

Conditional formatting & Dates – an introduction – Must read
Working with date & time values in Excel – a complete overview
Another ovredue items example – activities by employee

Christmas shopping list in Excel: conditional formatting to track budgets, bought items etc.
Employee shift calendar in Excel: Using dates, shift data to show busy & dull times.
Annual goals tracker: Track goals by % completed

The post Highlight due dates in Excel – Show items due, overdue and completed in different colors appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Multiple Find Replace with Power Query List.Accumulate()

Thu, 14 May 2020 07:57:00 GMT

Imagine you have a paragraph of text and you want to replace all occurrences of {four, normal, mysterious, nonsense} with {six, casual, confounding, handbags}. How would you do that?

You could use SUBSTITUTE() formula, but you need to nest four of them (as we need to replace four values with another four). But what if you have larger set of find / replacements?

Worry not, you can use Power Query to transform original text to new one by replacing all matching values.

In this page, learn how to do that with the excellent List.Accumulate() Power Query function.

The set up for multiple find / replace

Let’s start with two tables – mytext with the original text values and replacements with rules for replacement.

I have included a snapshot of these tables below.

Power Query transformations

Start by loading both of these tables to Power Query. We will transform mytext table to add a column with replaced text.

Before we could do the transformations, you must learn 3 key concepts.

They are,

List.Accumulate() Function
Getting Lists from Table columns
How to access individual list items

If you know these three concepts, skip ahead to next section. Else, read on.

Key concepts for multiple find replace

List.Accumulate() Function

List.Accumulate() function allows us to perform same action on items on a list and return a combined value.

A simple example for this is,

Imagine you have a list of 10 numbers {1..10}

You want to calculate the total of these numbers.

You can use List.Accumulate() to add them up. Of course, you can also use List.Sum() function, but this is a demo of List.Accumulate() dear.

Assuming the original list is in source, we can use List.Accumulate() to add up items like this:

=List.Accumulate(Source, 0, (state, current) => state+current)

The output of this step will be 55, sum of list with first 10 numbers.

What’s going on here?

List.Accumulate() is a cumulative function. It takes a list and a starting value (in our case these are source and 0) and applies a function on each of the list items while modifying the initial value.

Let’s see what that means for our list.

We start with 0 (call this starting state)
For each item in the list
- Update state to state + current (ie 0+1 initially, then 1+2, 3+3, 6+4…45+10)
At the end of the list, it returns the final state value, ie 55.

How to write the third argument of List.Accumulate()?

The third argument of List.Accumulate() is a function with two parameters – state, current.

State: this will be initially set to 2nd parameter of the function and changes every time List.Accumulate() moves down the list.
Current: this will the value of current list item

Power Query functions are written in this fashion.

(parameter1, parameter2…) => function definition

So, for example (state, current) => state+current is a function that takes 2 parameters and returns their sum.

To learn more about List.Accumulate():

Watch the video that accompanies this article. You will get clarity about List.Accumulate()
Read Datachant’s excellent List.Accumulate examples page.

Getting Lists from Table columns

If you have a table named replacements with columns [Find] and [Replace] in Power Query, you can use below syntax to extract a table column as list.

replacements[Find]

tablename[Column name]

How to access individual list items

If you have a list named source in Power Query with 10 items, you can access 5th item of the list with this syntax.

source{4}

So, for example, if you want to get 3rd item of the [Find] column in replacements table, use:

=replacements[Find]{2}

Note: Power Query uses 0 base for lists. So first item of the list will be list{0}

Using List.Accumulate for mass Find / Replace

Now that you are familiar with key concepts necessary, let’s do some replacements.

Go to mytext query and insert a custom column. In this column, we will generated replaced text.

Write this formula:

List.Accumulate(
    List.Numbers(0, Table.RowCount(replacements)), 
    [Text], 
    (state, current) => 
        Text.Replace(state, 
            replacements[Find]{current},
            replacements[Replace]{current}))

How does this formula work?

We create a list of numbers from 0. The size of this list will be same as number of rows in replacements table.
1. For our sample data, we get {0,1,2,3}
We start with input [Text] column value
We replace first replacements[Find] value with replacements[Replace] in [Text]
We repeat 3 step three, while updating the state
At the end of this process, we end up with [Text] that successively replaced all words in replacements table.

I know this formula is tricky to get your head around, but once you understand it, you will see the potential for other cool applications.

Download Multiple Find Replace Example file

Please click here to download the multiple find replace example file

List.Accumulate explained - Video

If you are still and not sure how List.Accumulate magic works, then please watch this video. It goes in to greater detail about this beautiful M function. See it below or visit my YouTube channel for it.

Chandoo

Merry Christmas & Happy New Year 2021

Thank you

This holidays…

Image Lookup – How-to show dynamic picture in a cell [Excel Trick]

Video - Dynamic Image Lookups in Excel

Step 1: Set up your data

Step 2: Write formula to lookup the image

Step 3: Insert a named range with the formula

Step 4: Set up a picture link to show image lookup result

Tips on using Image Lookup Technique

Download Picture Lookup Example File

Got questions about image lookups? Fire away...

9 Box grid for talent mapping – HR for Excel – Template & Explanation

What is 9 Box Grid?

How to create 9 box grid in Excel?

Interactive 9 Box Grid in Excel - Demo

Step by step instructions - Making 9 Box Grid template in Excel

Formula approach for Talent Map

Pivot Table approach for Talent Map

9 box grid for talent mapping - FREE Excel Template

Work in HR and use Excel? More resources for you...

6 Must Know Line Chart variations for Data Analysis

Line Chart Variations - Video

Indexed Line Chart

Sparklines

Spaghetti Line Chart

Smoothed Line Chart

Forecast Line Chart

Line Chart Combinations

Don't forget the labels ...

Download Line Chart Variations Workbook

Two-level Data Validation [Excel Trick]

Two-level data validation drop down - video

Download Sample File - Two-level Drop Downs

Step 1: Set up your source data

Step 2: Make a pivot table in outline layout

Step 3: Formulas to get the two-level structure

Step 4: Set up the validation rule

Cherry on top: Conditional formatting to flag errors

Whipped cream on top: Box drawing shapes + validation

Download Sample File - Two-level Drop Downs

More tips on data validation

Excel formula to convert calendar format to table

Calendar to Table Formulas – Video

Using SUMIFS formula

Limitations of SUMIFS approach:

Using INDEX formula

Download Example Workbook

Other ways to convert calendar format data

How would you deal with calendar style data?

Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks

Table of Contents

What is Power Query?

How does Power Query look?

Power Query Window - 6 Important Areas

How to activate Power Query?

Launching Power Query from Excel

Power Query from Power BI

Notes on Power Query availability

Power Query Tutorial - Video

Power Query as a Mind-map

How to use Power Query? Four Examples

Power Query Example 1: Load and Clean-up Employee Data

Step 1: Connect to the data set file from Excel

Step 2: Apply data cleansing steps in Power Query

Step 3: Extracting country from address to new column

Step 4: Publish data to Excel for analysis & reporting

Power Query Save & Load options – Explained

for more on this example…

Power Query Example 2: Scraping Web Data & Reshaping it

Step 1: Connect to web data source

Step 2: removing unnecessary rows

Step 3: Unpivoting the data

Power Query Web Connection Tips

for more on this example…

More Power Query Web Scraping Examples

Power Query Example 3: Combine data from all files in a folder

for more on this example…

Power Query Example 4: Joining and Appending Tables

How to use Power Query?
Four Examples

Power Query Example 1:
Load and Clean-up Employee Data

Power Query Example 2:
Scraping Web Data & Reshaping it

Power Query Example 3:
Combine data from all files in a folder

Power Query Example 4:
Joining and Appending Tables