Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ClemFandango
Helper II
Helper II

Importing column and converting into date

Hello PowerBI community,

I am in desperate need of help. I have spent about 2 weeks trying to do this and failed miserably.

All I need to do is convert a column into a recognisable date format.

The data is imported as text into a column called Source.Name. The date is shown as 020922.CSV (this is the name of the file imported). I have tried using Delimiter to split .CSV from 020922(uk date), but I get an error inside the column when converting ABC to date. I have also tried splitting 020922 into separate day, month and year columns and changing format to number, and adding leading zeros. This also doesn’t work.

Any idea how I can make a date column out of my Source.Name column?

Any help eternally appreciated,

CF

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @ClemFandango ,

 

Add a new custom column with the following calculation:

let xDate = Text.BeforeDelimiter([Source.Name], ".") in
Text.Combine(
    {
        "20" & Text.End(xDate, 2),
        Text.Range(xDate, 2, 2),
        Text.Start(xDate, 2)
    },
    "-"
)

 

This outputs as text in ISO format, allowing you to convert to any type/locale you need afterwards.

 

Example output:

BA_Pete_0-1670231741394.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

Hi @ClemFandango ,

 

Add a new custom column with the following calculation:

let xDate = Text.BeforeDelimiter([Source.Name], ".") in
Text.Combine(
    {
        "20" & Text.End(xDate, 2),
        Text.Range(xDate, 2, 2),
        Text.Start(xDate, 2)
    },
    "-"
)

 

This outputs as text in ISO format, allowing you to convert to any type/locale you need afterwards.

 

Example output:

BA_Pete_0-1670231741394.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thank you so much. You have no idea how long i previously spent on this. Your suggestion works wonderfully. 

Smalfly
Resolver III
Resolver III

Hi @ClemFandango ,

 

try these steps:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY0NDIy1EsuLlOK1YlWMjA0APIRXCMUrjGqrJGFgRGKrCGcGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".csv","",Replacer.ReplaceText,{"Source.Name"}),
#"Added Custom Column" = Table.AddColumn(#"Replaced Value", "Custom", each Text.Combine({Text.Middle([Source.Name], 2, 2), "/", Text.Start([Source.Name], 2), "/", Text.Middle([Source.Name], 4)}), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", type datetime}})
in
#"Changed Type1" 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors