Power Query Date Field

[aggie81]

How can I change an international date type from SQL inside Power Query to an American date field?
The data from SQL comes out as 20140620 and I want to change it to mm/dd/yyyy or 06/20/2014.
Any help is greatly appreciated.
Thanks,
Lee

 

[Harry0]

We can go the cheap rough and use your number as a helper cell to be on A1 and this formula to be on B1. Obviously you change A1 to the cell location.
=MID(A1,5,2)&"/"&MID(A1,7,2)&"/"&MID(A1,1,4)

For Mid (you select the cell where it is located, the number to start within the cell, and then how long the thing is). & is to add an extra function, and the "/" is to insert the separation.

If you want to add another cell to to do addition to add a date you would need to change the cell formula to a date format. Which you would need to right click, select format cell, select date, and the type of date style you want, or you can go to custom and put it your own style. dd,mm,yyyy or any variation like ddd,mmm,yy

 

[aggie81]

Thanks for the reply but this is inside Power Query which is a .com add-in for Excel.
I think the formula is something like from what I've found on Microsoft website but I can't get it to work.

Date.FromText("2010-02-19") equals Date,YYYY-MM-DD

Your formula works better if it is something like
=Date(Left(),Mid(),Right()) and then format the cells to your flavor.

I have tried merging the query from SQL with an Azure Marketplace Extended Calendar database. I can get the date to merge into the Power Query but it doesn't behave properly in the Pivot Table. I can't do Grouping on the date field from inside the Pivot Table but I can get the date field displayed in way that is more pleasant to the eye. I really wanted to Group the dates.
Lee

 

[David Evans]

How can I change an international date type from SQL inside Power Query to an American date field?
The data from SQL comes out as 20140620 and I want to change it to mm/dd/yyyy or 06/20/2014.
Any help is greatly appreciated.
Thanks,
Lee

Hi Lee -

If you download the Calendar file from Datastream - this is what the link to it looks like in my PQ
= #"https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/"{[Name="BasicCalendarUS"]}[Data]

You can then relate your SQL data to that calendar table and get the date in the format you are looking for I believe ... the format you have is Dateint ...

 

[aggie81]

Thanks, I've used the file you refer to in some Power Pivot tables and I'm very thankful that he did that for everyone.
I used it in my Power Query but still can't get Pivot Table to Group like a normal pivot table. It may be do to the merges that I have to do as it will not Group on any field that I try. Have you had any experiences using Power Query with normal Pivot Tables?

 

[David Evans]

Thanks, I've used the file you refer to in some Power Pivot tables and I'm very thankful that he did that for everyone.
I used it in my Power Query but still can't get Pivot Table to Group like a normal pivot table. It may be do to the merges that I have to do as it will not Group on any field that I try. Have you had any experiences using Power Query with normal Pivot Tables?

I'm currently using it and I've used it previously on a Medical record project earlier this year. I use Power Query to filter the Calendar Table down to a reasonable size in PP - otherwise you get all the dates since 1901 and typically we need quite narrow date windows - a few quarters in my case ...

I have seen issues with grouping dates previously in Power Pivot, but I don't recall the details

Sanitize and reduce your data to a reasonable size and post the file here - this lot around here will solve it 99 times out of 100.

There's are PowerPivotPro site that is also very good ...

 

[Khalid NGO]

Hi,
Try:
=DATEVALUE(RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4))

Not sure how it works with PQ