Forum Discussion
Insert 12 Rows for each ID in Power Query
Hey Guys,
I have a dataset, I want to insert 12 rows for each ID in the table using Power Query and insert blank in other records except ID. How can I do that?
Data:
| ID | Month Year | Sales |
| 1 | May-22 | 20 |
| 2 | Jun-22 | 23 |
| 3 | Jul-22 | 50 |
| 4 | Aug-22 | 100 |
Required Table:
| ID | Month Year | Sales |
| 1 | Jan-22 | 0 |
| 1 | Feb-22 | 0 |
| 1 | Mar-22 | 0 |
| 1 | Apr-22 | 0 |
| 1 | May-22 | 20 |
| 1 | Jun-22 | 0 |
| 1 | Jul-22 | 0 |
| 1 | Aug-22 | 0 |
| 1 | Sep-22 | 0 |
| 1 | Oct-22 | 0 |
| 1 | Nov-22 | 0 |
| 1 | Dec-22 | 0 |
| 2 | Jan-22 | 0 |
| 2 | Feb-22 | 0 |
| 2 | Mar-22 | 0 |
| 2 | Apr-22 | 0 |
| 2 | May-22 | 0 |
| 2 | Jun-22 | 23 |
| 2 | Jul-22 | 0 |
| 2 | Aug-22 | 0 |
| 2 | Sep-22 | 0 |
| 2 | Oct-22 | 0 |
| 2 | Nov-22 | 0 |
| 2 | Dec-22 | 0 |
7 Replies
HI hamzashafiq
In order to achieve the desired result, follow these steps
1. Load the IDs and months table2. Append it to your sales table
3. Add to the result table column concatenates date with ID
4. Remove Duplicates (based on the merged column)
5. Replace null with zero on the sales column :5. Remove unnecessary column ("merged")
6. Uncheck "enable load" on unnecessary table
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks Ritaf1983 for the solution but the problem is that, how to load the IDs and Months data, that ID is not static. We have thousands of Ids.
pls see my vedio
https://1drv.ms/v/s!AiUZ0Ws7G26RiA1EU-mEk-_MNJP4?e=INkV42Share sample pbix file to help you.
https://1drv.ms/u/s!AiUZ0Ws7G26RiAxMKyoCpi8iYEy9?e=0AsQV5List.Generate(()=> #date(Date.Year([Month Year]),1,1), (x)=>x<=#date(Date.Year([Month Year]),12,31), (x)=>Date.AddMonths(x,1))THank you Ahmedx let me try the solution.
Hi Ahmedx I tried your solution and it's working thank you. Now let's see how the actual data looks like.
Data ID Group Year Month Transaction Amount 1 History Start Jan-23 10 1 Sales Feb-23 20 1 Sales Mar-23 30 1 Sales May-23 40 2 History Start Mar-23 10 2 Sales Apr-23 20 2 Sales May-23 30 2 Sales Jul-23 40 In the data you see for each ID (1 and 2) one of the month is missing (ID 1 miss April and ID 2 miss June), we need to insert only that row which is missing, the insertion should start from the date where Group = "History Start" and end at Max date of "Year Month" for each ID. Below is the required output.
Output ID Group Year Month Transaction Amount 1 History Start Jan-23 10 1 Sales Feb-23 20 1 Sales Mar-23 30 1 Sales Apr-23 0 1 Sales May-23 40 2 History Start Mar-23 10 2 Sales Apr-23 20 2 Sales May-23 30 2 Sales Jun-23 0 2 Sales Jul-23 40
