Insert 12 Rows for each ID in Power Query

[hamzashafiq]

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

[Ahmedx]

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))

[hamzashafiq]

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 · Accepted solution]

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26RiCQ0vENo20mrtc9h?e=vFzU7q

[hamzashafiq]

@Ahmedx Perfect, Thank you so much.

[hamzashafiq]

THank you @Ahmedx let me try the solution.

[Ritaf1983]

HI @hamzashafiq 
In order to achieve the desired result, follow these steps
1. Load the IDs and months table

2. 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

Link to sample file 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[hamzashafiq]

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.