date from power query

[aggie81]

I have a Power Query that returns a date as 20130910 for September 10, 2013. I can get the date in Excel 2013 but every time I refresh the Power Query it removes my column or my query gets corrupted and I have to redo. The M. language is hard for me to follow and I can't find an example of a date conversion on the web.
Does anyone have any experience with this?
Thanks,
Lee

 

[NARAYANK991]

Hi Lee ,

I don't know anything about this , but I think if you post the query code , we can get to the solution.

Narayan

 

[aggie81]

The query is listed below as it appears in Power Query. It was suggested that I try it because Power Pivot isn't available in Excel 2013 stand alone version yet. I change the date 20130901 to Sept 1, 2013 in the worksheet by putting it on the last column of the returned query. If I refresh then it deletes the column. If I put the dat column inside the query it causes it to hang and corrupt the file.
Thanks,



let
Source = Sql.Databases("sbs11"),
JJLDAT = Source{[Name="JJLDAT"]}[Data],
OEINVH = JJLDAT{[Name="OEINVH"]}[Data],
FilteredRows = Table.SelectRows(OEINVH, each [SHIPDATE] >= 20080101),
RemovedOtherColumns = Table.SelectColumns(FilteredRows,{"INVUNIQ", "ORDNUMBER", "CUSTOMER", "VIADESC", "SHIPDATE", "INVNETNOTX", "INVITAXTOT", "INVNETWTX", "INVNUMBER"})
in
RemovedOtherColumns

 

[DBExcel]

you need to have some code to transform the column type to a date. In my experience Power Query needs to be told explicitly that a column is dates or you will encounter problems. once you have dates, you will be golden. you need something like Date.From or text.todate. not sure which is legit without the formula library in front of me. Once I know that I ended up manually splitting the column into day, month and year, and then backing into the date, but I know it can be done in one step. post a sample file for better assistance. also, if you have any bad or missing dates in the column, that can throw PQ for a loop.