Share [Social9_Share]

Repeat Row N Times

Repeat Row N Times 1

A while ago I met the Head of Human Resources for a Financial Services Company. And you can very well guess that the nature of the meeting sure had to do with solving a peculiar spreadsheet problem.

Here is (a part of the problem) what her team was grappling with

 

Assume this tiny Training Data

Repeat Row N Times

  1. The first 2 columns are employee details
  2. The rest of the columns are the names of the training programs conducted
  3. The numbers represent the number of times the person has undergone that training
  4. If the cell is blank that means that he has not undergone that training

This sounds simple right ?

But this is a terrible way of storing data. If you are unsure WHY?? Let me throw one questions at you

Question – How many unique training programs did each employee take ?

I think I got you now, but in case you aren’t convinced please note that the real time data will not be as tiny as the snapshot. You’ll have hundreds of employees and dozens of training.

That makes it hard to do it manually

 

Here is the Output expected..

Repeat Row N Times 2

  1. Every instance (marked as 1 or 2) should be transformed in a single row
  2. If the employee has attended two training his record will appear twice
  3. If the employee has attended no training, there will not be any record

 

I found a way to solve this using PowerQuery

 

Quick facts

  1. This solution is dynamic i.e. it’ll work fine if you add more employees (rows) or more training programs (columns)
  2. In case you want to read more about Table.Repeat Function

 

You can DOWNLOAD THE EXCEL FILE HERE

 

How would you have solved this problem ?

I have found many companies capturing similar data incorrect way. Please post your solution (powerquery or otherwise) in the comments. I would love to see multiple ways of solving this

 

  • GraH

    Nice, another M-function demystified. I hope you post a video for all of the 460 something that are available in M. :-p

    • Haha.. that’s long way to go! I am not done with Excel functions yet 😀

      • GraH

        What are you waiting for? 😀

  • mma173

    I came across something similar at work and came-up with a solution using formulas.

  • Ryan

    Thank you for sharing the video…I have much to learn! With that said, if the goal is just to know the number of distinct trainings attended, a regular Count function will yield the correct results…is there something else to this that I am missing?

  • Asif Hafeez

    I know I am bit late but solved this using Pivot Table, multiple consolidation ranges and then un-pivot, to shape it like a data base. After a little data cleansing, applied simple pivot table by counting values. Its not dynamic, so vote is for power query.

Chandeep Chhabra

Popular Posts

© Goodly