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.
Hi Folks,
I have a date column (below snapshot) where the format post downloading the dump is incorrect. The format is dd-mm-yy but I need to change the date from 04-Dec-24 to 12-Apr-24 which is actually correct date (somehow day and month got interchanged post download). Also in the same column some date is in text format and need to convert it to dd-mm-yy format.
Please let me know if above transformations can be done in power query
maybe you can create a custom column in PQ
=#date(Number.From(Text.AfterDelimiter([date],"/",1)),Number.From(Text.BeforeDelimiter([date],"/")),Number.From(Text.BetweenDelimiters([date],"/","/")))
Proud to be a Super User!
If you are starting with dates like this
And you want to convert to dd/mm/yy so that the first row is 12th Apr 24, what are the 4th, 5th and 6th rows?
You can't mix formats in the same column so if you want to apply dd/mm/yy to rows 1 through 3, you must do the same with the rest of the rows. But you can't because there isn't a month 28, 27 or 26.
So either your data is in mixed formats i.e. dd/mm/yy and mm/dd/yy or it's all in the same format which is dd/mm/yy.
If it's in mixed formats then you need to fix that in the source data.
So what you are asking can't be done with the data you are showing me.
Regards
Phil
Then choose Date Type : Date and Locale : English (United States)
Proud to be a Super User!
@PhilipTreacy I guess the issue is at data source. The same column has mix of date formats as well as mix of data types.
Going with your snapshot,
Not sure why you are saynig some rows are dates and some rows are text. In this image all the rows are text - you can tell because the data type icon in the column header is ABC
If you are certain that some rows are dd/mm/yy and others are mm/dd/y then the only way to fix that is by fixing the source data so the data is in one format only.
Phil
Proud to be a Super User!
The dates you are importing are in the format m/d/y but you are using a PC that uses d/m/y.
In this case you need to do what's called importing dates using locale - this blog post explains all
Change Type Using Locale with Power Query
regards
Phil
Proud to be a Super User!
@PhilipTreacy Thanks for yoru time and help but it still see the same date 04-Dec-2024 instead of 12-Apr-24. Also, the date in text format throws an error while doing above steps in power query.
User | Count |
---|---|
76 | |
74 | |
61 | |
61 | |
45 |
User | Count |
---|---|
108 | |
103 | |
93 | |
83 | |
64 |