Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hamzashafiq
Kudo Collector
Kudo Collector

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:

IDMonth YearSales
1May-2220
2Jun-2223
3Jul-2250
4Aug-22100

 

Required Table:

IDMonth YearSales
1Jan-220
1Feb-220
1Mar-220
1Apr-220
1May-2220
1Jun-220
1Jul-220
1Aug-220
1Sep-220
1Oct-220
1Nov-220
1Dec-220
2Jan-220
2Feb-220
2Mar-220
2Apr-220
2May-220
2Jun-2223
2Jul-220
2Aug-220
2Sep-220
2Oct-220
2Nov-220
2Dec-220
1 ACCEPTED SOLUTION
7 REPLIES 7
Ahmedx
Super User
Super User

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   
IDGroupYear MonthTransaction Amount
1History StartJan-2310
1SalesFeb-2320
1SalesMar-2330
1SalesMay-2340
2History StartMar-2310
2SalesApr-2320
2SalesMay-2330
2SalesJul-2340

 

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   
IDGroupYear MonthTransaction Amount
1History StartJan-2310
1SalesFeb-2320
1SalesMar-2330
1SalesApr-230
1SalesMay-2340
2History StartMar-2310
2SalesApr-2320
2SalesMay-2330
2SalesJun-230
2SalesJul-2340

@Ahmedx Perfect, Thank you so much.

THank you @Ahmedx let me try the solution.

Ritaf1983
Super User
Super User

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

Ritaf1983_0-1684203638476.png

2. Append it to your sales table

Ritaf1983_1-1684203758367.png

3. Add to the result table column concatenates date with ID

Ritaf1983_2-1684203890732.png

4. Remove Duplicates (based on the merged column) 
5. Replace null with zero on the sales column :

Ritaf1983_3-1684204121306.png

5. Remove unnecessary column ("merged")

Ritaf1983_4-1684204322889.png

 

6. Uncheck "enable load" on unnecessary table

Ritaf1983_5-1684204369652.png

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



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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors