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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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



Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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