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))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 Ahmedx Perfect, Thank you so much.
