Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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"