Welcome to Excel Jet Consult blog post. In this Part 2, I demo how to, “Part 2: Repeat All Products By Units Sold Frequency Distribution using Power Query.” Le’ts get started
Video
Sample Data
We are going to use the same dataset used in the formula based solution (Part 1) as seen below
Power Query Operation
- The first thing we need to do is get the dataset into the Power Query editor. Since the dataset is already formatted as an Excel Tables using CTRL + T, we just need to click inside the dataset, right-click and select Get Data from Table/Range.
That will get the dataset into the Power Query editor as seen below:
- Next, we want to use the Advanced Editor to write the M code. Therefore, In the Advanced group of the Power Query View tab, click on Advanced Editor.
- In the Advanced Editor dialogue box, I used the Table.AddColumn M function and provided the input values for the three parameters and passed the ListRow name to the in as seen below:
- Click OK
From the screenshot below, a new column named, mylist is added. The list contains the Units Sold frequency distribution.
- The next thing is to click on the double upward facing expandable arrows located in myList column.
- Then, Select expand to new rows.
From the screenshot below, we can see that the Products are repeated based on the units sold frequency distribution
- Finally, we can load the result to the Excel worksheet using the Close and Load To in the Power Query Home tab. In this example, the result is loaded to cell F1 as seen below:
See you in the next tutorial