Effective from and to dates

[MohammedAli]

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.

[v-eqin-msft]

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

[v-eqin-msft]

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

[pranit828]

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