Forum Discussion
Solved
Unpivot and date fill
I have the data in this format
| ID | SN | Item Description | Start Date | Month 1 | Month 2 | Month 3 |
| 110 | 1 | Printer cartridge | 1 Apr 2020 | 10 | 20 | 0 |
| 111 | 2 | Ruler | 1 Nov 2020 | 20 | 30 | 5 |
How do I unpivot and fill in the month so that the table looks like this
| ID | SN | Item Description | Start Date | Month | Quantity |
| 110 | 178 | Printer cartridge | 1 Apr 2020 | 1 Apr 2020 | 10 |
| 110 | 178 | Printer cartridge | 1 Apr 2020 | 1 May 2020 | 20 |
| 110 | 178 | Printer cartridge | 1 Apr 2020 | 1 Jun 2020 | 0 |
| 111 | 2 | Ruler | 1 Nov 2020 | 1 Nov 2020 | 20 |
| 111 | 2 | Ruler | 1 Nov 2020 | 1 Dec 2020 | 30 |
| 111 | 2 | Ruler | 1 Nov 2020 | 1 Jan 2021 | 5 |
2 Replies
Hi gancw1 ,
First select columns "ID", "SN", "Item Description", "Start Date" and choose unpivot other columns.
Then create a new column with this formula:Month =
Date.AddMonths([Start Date],Number.FromText(Text.End([Attribute],1))-1)
If I answered your question, please mark it as a solution to help other members find it more quickly.
