March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Solved! Go to Solution.
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 | |||
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 |
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |