Forum Discussion
Solved
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 ...
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=0AsQV5
List.Generate(()=>
#date(Date.Year([Month Year]),1,1),
(x)=>x<=#date(Date.Year([Month Year]),12,31),
(x)=>Date.AddMonths(x,1))
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 |
Ahmedx Perfect, Thank you so much.