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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ClemFandango
Helper III
Helper III

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 IV
Resolver IV

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors