Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to Solution.
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:
Pete
Proud to be a Datanaut!
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:
Pete
Proud to be a Datanaut!
Thank you so much. You have no idea how long i previously spent on this. Your suggestion works wonderfully.
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"
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |