Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
My data is in the following format
Emp ID | Pay Type | Effective From | Effective To | Amount |
100001 | Basic | 1/1/2016 | 31/1/2017 | 1500 |
100001 | Basic | 1/2/2017 | 30/6/2018 | 2000 |
100001 | Basic | 1/7/2018 | 31/12/2019 | 2500 |
100001 | Basic | 1/1/2020 | null | 3000 |
100001 | HRA | 1/1/2016 | 31/1/2017 | 150 |
100001 | HRA | 1/2/2017 | 30/6/2018 | 200 |
100001 | HRA | 1/7/2018 | 31/12/2019 | 250 |
100001 | HRA | 1/1/2020 | null | 300 |
i want to transform the data to
Emp ID | Month | Year | Basic | HRA |
100001 | Jan | 2016 | 1500 | 150 |
100001 | Feb | 2016 | 1500 | 150 |
100001 | Mar | 2016 | 1500 | 150 |
... | ||||
100001 | Feb | 2017 | 2000 | 200 |
... | ||||
100001 | Feb | 2020 | 3000 | 300 |
it will be nice if you could please guide me in the right direction on how to achieve this.
Thanks in advance.
Hi @MohammedAli ,
Did I answer your question ? Please mark my reply as solution. Thank you very much~
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @MohammedAli ,
You could follow these steps:
1. Pivot the "Pay type" column and use "Amount" column as values.
2. Change data type of "Effective From/To" columns to Date.
My transformed data and visualization look like this:
Is the result what you want? If not, please upload some data samples and expected output.
Please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
Hi @MohammedAli ,
In Power Query Editor duplicate the date column and transform it to year as below and duplicate again and transform it to Month.
After transforming these will be avaialble as columns for you to use in the report.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Regards,
Pranit
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |