cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MohammedAli
Frequent Visitor

Effective from and to dates

Hi,

My data is in the following format 

Emp IDPay TypeEffective FromEffective ToAmount
100001Basic1/1/201631/1/20171500
100001Basic1/2/201730/6/20182000
100001Basic1/7/201831/12/20192500
100001Basic1/1/2020null3000
100001HRA1/1/201631/1/2017150
100001HRA1/2/201730/6/2018200
100001HRA1/7/201831/12/2019250
100001HRA1/1/2020null300

 

i want to transform the data to 

 

Emp IDMonthYearBasicHRA
100001Jan20161500150
100001Feb20161500150
100001Mar20161500150
...    
100001Feb20172000200
...    
100001Feb20203000300

 

it will be nice if you could please guide me in the right direction on how to achieve this.

 

Thanks in advance.

3 REPLIES 3
Eyelyn9
Community Support
Community Support

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

Eyelyn9
Community Support
Community Support

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:

8.13.4.PNG8.13.5.PNG

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

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.

pranit828_0-1597290891665.png

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit

 




PBI_SuperUser_Rank@1x.png


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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!