Issue with Dates in Column heading

masterelaichi
Posts: 26
Joined: Fri Sep 02, 2016 8:36 am

Issue with Dates in Column heading

Postby masterelaichi » Tue Jun 27, 2017 8:49 am

Hi,

I am having some problems working with dates in Power Query/BI. I was sent an excel file which has dates as column headers, in the format May-14,June-14,Jul-14, Aug-14....May-17. When I load this into Power Query and unpivot the date columns, I get all the dates in a single column as expected. The problem is that it imports the dates incorrectly. As the source file has data for a particular month and not individual days, Power Query imports the dates as 14/5/2017, 14/6/2017...14/12/2017, 15/1/2017,15/2/2017, etc, due to which I get blanks in my report for any date prior to 2017

I have checked the source datatypes and it is set to "Date". I have also checked the datatype in power query. Any suggestions on how to fix this?

Thank you

MattAllington
Posts: 881
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Issue with Dates in Column heading

Postby MattAllington » Tue Jun 27, 2017 9:28 am

How about pre-pending "1-" in front of the data in the column before converting to date?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

masterelaichi
Posts: 26
Joined: Fri Sep 02, 2016 8:36 am

Re: Issue with Dates in Column heading

Postby masterelaichi » Tue Jun 27, 2017 3:29 pm

Hi Matt,

Thanks for your help. I managed to do this using a slightly convoluted way using the "Use Locale" function and the date parsing function. Eventually, I somehow got it to work. Although, your solution would have been a lot simpler!

As an aside, I notice that you are now conducting Power Query Training online. Any plans of conducting a class-room training for Power Query similar to your Power Pivot Training?

MattAllington
Posts: 881
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Issue with Dates in Column heading

Postby MattAllington » Tue Jun 27, 2017 4:50 pm

I don't have plans for Power Query live training as I think this subject lends itself very nicely to video training. Power Pivot is different because it is quite tricky in places and needs some live instruction. Even my online Power Pivot classes have a "live" component, but I don't think this is necessary for PQ. There is a student "ask the teacher" feature as part of my PQ training so you can ask for any clarification.

Regards

Matt
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest