PowerBI unable to detect time type in get data
I get data from a folder where my file name has format Watches_20220315_095127 its in date and time format. But Power BI is unable to detect when the time starts with "0" i.e 095127 it displays error. Can you please help me. I need that time from file name to be used in powerbi report. Power BI works fine when my time has the following format.
Watches_20220315_105127 Watches_20220307_184253 Watches_20220301_144421
PowerBI not detecting as time for below format Watches_20220315_095127
Assuming that you have the file name as a column in Power Query (PQ), you need to convert that text into a format that PQ can turn into a Date. You can do something like this by getting the sections of the string using Text.Range (Where [Column1] is the name of your column).
DateTime.FromText(
Text.Range([Column1], 8, 4) & "-"
& Text.Range([Column1], 12, 2) & "-"
& Text.Range([Column1], 14, 2) & " "
& Text.Range([Column1], 17,2) & ":"
& Text.Range([Column1], 19,2) & ":"
& Text.Range([Column1], 21,2)
)
This will split out the text into a YYYY-MM-DD HH:MM:SS format that can use DateTime.FromText to convert it into a date time.
You will have to set the field to be a date time type for PQ and then DAX to pick it up as a date time. In the below example the 4th row that has a time of 09:30:01 will now be converted to a correct date time.
