Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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.
User | Count |
---|---|
86 | |
82 | |
68 | |
66 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |