Category: POWER QUERY

After filtering a dataset, allow the user to display only specific columns in the result

{ 2 Comments }

Here is a simple 5 column dataset showing basic employee information. The objective is to show only those rows of data in which the Score (column D) is greater then 3.  While one can solve this with a simple filter, the solution will not be dynamic.  To get a dynamic solution, one may use the […]

Read More →

Segregating data appearing in a single column into multiple columns where there is blank row between records

{ 0 Comments }

Here is a sample dataset downloaded from an external application.  As you can observe, there are anomalies in the dataset: All the data appears in a single column when Infact it should be segregated into different columns; and There is no separating one record from another.  All we know is that each record ends with […]

Read More →

Tabulating data from multiple unstructured Excel files

{ 0 Comments }

Many a times data downloaded from Applications/ERP’s are not in a filter/Pivot ready format.  In such cases, a lot of time has to first be invested in getting that data in proper order before even beginning to analyse that data.  What makes this situation worse is that data is downloaded every month in that unstructured […]

Read More →

Append data from multiple worksheets of multiple workbooks where each worksheet has a different heading

{ 0 Comments }

In a folder there are multiple workbooks with an unknown number of worksheets in each workbook.  Each worksheet has data for one year and has 13 columns – the first is for the Product and the other 12 are for each month of the year.  So sheet1 of Book1 has Product in column1, 1 Jan […]

Read More →

Determine the top selling location for each product

{ 0 Comments }

Visualise a 3 column dataset as shown below – Location, Product and Sales.  Each location can have multiple products (Product A has Banana, Apple and Carrot) and each product can be sold in multiple locations (Banana is sold in locations A, B and F). The objective is to determine the location with highest sales for […]

Read More →

Remove duplicates from each cell of a dataset

{ 1 Comments }

Here’s a dataset with 4 columns and 2 rows.  In column A, there is Patient ID and in the other columns are observations recorded by 3 Doctors for each patient.  As can be seen, in each cell there are multiple observations which are either separated by Alt+Enter (another line in the same cell) and/or a […]

Read More →

Merge 2 work schedules

{ 2 Comments }

In a certain Organisation, assume that there are two work schedules – one that runs from Friday to Thursday and another that runs from Sunday to Saturday.  In the image below, Work schedule 1 is in range A2:C4 and Work schedule 2 is in range E2:G4.  In each dataset, the start date is the first […]

Read More →

Rearrange a multi heading dataset into a single heading one which is Pivot ready

{ 8 Comments }

Any well arranged dataset should be “Pivot Table” ready with the following 3 important properties: There should be no merged and centered cells; and Every column should have a unique heading; and Every column should have only 1 heading Here’s one dataset which violates all rules mentioned above. Headings in row 1 are merged; and […]

Read More →

Rearrange travel data to clearly show travel from and travel to locations

{ 0 Comments }

Consider a 2 column dataset as shown below User Location AAA Tokyo AAA Osaka AAA Nagoya AAA Hakone AAA Kyoto BBB Sapporo BBB Nara CCC Tokyo CCC Hakone CCC Osaka DDD Osaka DDD Tokyo Customer AAA travelled from Tokyo to Osaka, Osaka to Nagoya, Nagoya to Hakone and Hakone to Kyoto.  All locations appear in […]

Read More →

Sort, comma separated entries appearing in a cell, in ascending order

{ 10 Comments }

Assume a single column dataset as shown below.  As you can observe, there are multiple entries appearing the same cell separated by commas. The objective is to sort, in ascending order, the entries in each cell.  The expected result is shown below. I have solved this problem using Power Query a.k.a Data > Get & […]

Read More →