Rearrange text in a cell (unusual date format)

[Anonymous]

Our system outputs dates in a way I have never seen. 

YYYYMMDD

With no dashes etc.

I need help converting these into a traditional American format. 

ie MM-DD-YYYY

My final report will need to be able to filter out dates so this is a must.

I am writing a report to make payment proposals easier, as now it requires between 4 and 9 spreadsheets copying and pasting etc. etc. So I will need to be able to pick a specific date in the future and filter those that are after out.

[Ahmedx]

pls try this

= Date.FromText([inv date],[Format="yyyyMMdd", Culture="en-US"])
------ or ----
= Date.FromText( Text.From( [inv date]),[Format="yyyyMMdd", Culture="en-US"])

[rajendraongole1 · Accepted solution]

Hi @Anonymous - To convert the date format from YYYYMMDD to the traditional American format MM-DD-YYYY in Power BI

In Power query editor take a custom column use below function:

Date.FromText(Text.Middle([Date Column], 5, 2) & "-" & Text.End([Date Column], 2) & "-" & Text.Start([Date Column], 4))

[Anonymous]

ok not to sound daft... but where and how do I enter this?

I am very new and when i tried this i kept getting an error

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjEwNLBQitWBciwMjZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"inv date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"inv date", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [inv date],each let y = Int64.From([inv date]/10000),m = Int64.From([inv date]/100-y*100),d=[inv date]-m*100-y*10000 in #date(y,m,d),Replacer.ReplaceValue,{"inv date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"inv date", type date}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.