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?
