Today we are going to discuss about the method to repeat items based on count column using Power query. This is the first time we are going to discuss about power , I am so excited today. We will have lots of M-Code posts in this blog in coming days.
Let me show you the requirement and expected result.
In the range C3:D7, names and count details are there in adjacent columns.
Our task is to repeat the names based on count. You can see that in the range G3:G21, 'Mike Girvin' is getting repeated 4 times and 'Leila Gharani' is getting repeated 5 times and so on.
Lot to learn, Lets dive in.
Click anywhere in the range C2:D7, press Ctrl + T or Ctrl + L, to convert the data set into Table. 'T' meant for table, 'L' meant for list. Earlier the same data set has been called as list, so they still kept that shortcut.
Create Table dialog box will open,
In our case 'My data has Headers' check box already checked. This is because, excel understood that first row is header, since there is a difference in data type.
If this check box is not checked, please check on that and click OK.
Now, you can see that contextual tab "Table Design" is getting added in menu bar.
Click on the that tab, in left most corner, in properties group, click on the table name box and change the name based on your preference. Conventional naming methods are there to name the table for best practice. Here, I named it as tNames.
Now, click anywhere in the table and press Alt + A + P + T, this is the shortcut key to get the data from Table to Power query editor.
We can get the data from table to power query through user interface as shown below.
Either of the above 2 methods we can get into power query editor,
In Add column menu, click on the custom column in the left most corner. Custom column dialog box will open as shown below.
In 'New column name' text box, feed proper column name based on your preference, in our case I gave "List". In the custom column formula bar, press '={1..[Count]}'
In the bottom part you can see, 'No syntax errors have been detected', that means our formula is correct, then click OK. Above formula is the replacement of Row and Indirect function construction in excel.
See in the query steps portion at right, one step is got added as 'added custom'.
We can change the name by clicking F2 key after selecting the step.
In the formula bar, M-code is generated for the activity, what we did through user interface.
Power query is case sensitive, and base is zero. We will learn lot about M-Code in future posts. Now, we will get back to our requirement.
Column named "List" is getting added in the table, and this contains list. At top right corner of list column, you can see two up arrows in opposite direction. Click on that button and select 'Expand to New rows' as shown below.
After expanding to new rows, output will look like below.
On the header of Count column, click once and after pressing shift click on list column header, both of this column got selected, then press right click in the mouse as shown below.
click on remove column option. We deleted 2 columns, and you can see that step got added for each activity. Click on the drop down for Close & Load option in top left corner in Home tab, and then click Close & Load.
Now, the table loaded in new excel sheet as shown below.
When you do this in real time, it will take hardly 20 seconds to do this task with ease in process. We can achieve most of our data cleansing tasks through user interface in power query with minimal clicks. In further posts, we are going to discuss a lot about Power query.
Comentários