Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Every month I load in an excel file into my Power BI template. The spreadsheet has a column formatted as text which represents the date and looks something like "80123" which represents August 1, 2023. To convert the text to date format of 8/01/2023, I have the formula as
= Table.AddColumn(#"Changed Type1", "Custom", each Text.Combine({Text.Start(Text.From([Date], "en-US"), 1), "/", Text.Middle(Text.From([Date], "en-US"), 1, 2), "/2023"}), type text)
But, whe I load in a file for months that have two digits for the month such as "100123" the formula only works the same if I change it to = Table.AddColumn(#"Changed Type1", "Custom", each Text.Combine({Text.Start(Text.From([Date], "en-US"), 2), "/", Text.Middle(Text.From([Date], "en-US"), 2, 2), "/2023"}), type text)
Is there perhaps a different formula I can use so that I don't have to keep changing it where it can convert the text to the correct date format regardless how many digits the month has?
pls try this
Date.FromText( Text.PadStart(Text.From(
[date]),6,"0"), [Format = "MMddyy"])
There will be another formula but why don't you pad the text using :
Text.PadStart(theColumn, 6, "0")
and use the 2nd formula on everything.