cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

 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
1 ACCEPTED SOLUTION
Super User
7 REPLIES 7
Super User

pls see my vedio
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),
Kudo Collector

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
Super User
Kudo Collector

@Ahmedx Perfect, Thank you so much.

Kudo Collector

THank you @Ahmedx let me try the solution.

Super User

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

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

Kudo Collector

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors