Error when sorting rows

[Syauqi]

I encountered an error when sorting rows. I sorted two columns with Text and Whole Number types in ascending order. The column that caused the error was the one with the Date data type. I made sure that the date format was correct for each row by using “Using Locale,” but that didn't help. Thank you for your help.

[v-achippa]

Hi @Syauqi,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Mauro89, @Royel and @ronrsnfld for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Syauqi,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Royel · Accepted solution]

Hi @Syauqi I have loaded same formated data and solve this in a optimal way 

here it is before the fix: 

After Fixing: 

Complete M-Code: 

let
    Source = Table.FromRows(
        {
            {"1",  "Mon 7/21/25", "Wed 7/23/25"},
            {"2",  "Thu 7/24/25", "Mon 7/28/25"}
        },
        {"TaskID",  "Start_Date", "Finish_Date"}
    ),

    CleanAndConvert = Table.TransformColumns(
        Source,
        {
            {"TaskID", each Number.FromText(_), Int64.Type},
            {"Start_Date", each Date.FromText(Text.AfterDelimiter(_, " "), [Culture="en-US"]), type date},
            {"Finish_Date", each Date.FromText(Text.AfterDelimiter(_, " "), [Culture="en-US"]), type date}
        }
    ),

    Sorted = Table.Sort(CleanAndConvert, {{"Start_Date", Order.Ascending}})
in
    Sorted

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

I’d love to stay connected. Join me on LinkedIn for more tips, learning paths, and real-world Fabric & Power BI solutions.

[Mauro89]

Hi @Syauqi,

When sorting by a Date column in Power Query and you get an error, it usually means one of the values is not a true date type (even if it looks correct). This often happens when:

• There are hidden text values such as spaces or non-printable characters
• Some rows contain invalid dates (e.g., blank, “-”, “N/A”)
• The column is only formatted like a date, not actually typed as Date

How to fix it:

1. Check column type
   
• Right-click the Date column → Change Type → Date
• If needed: Change Type → Using Locale → Date (your locale)
2. Detect problematic rows
   
• Add a new column:
  = try [DateColumn] otherwise "Error"
• Filter the new column for Error
3. Fix or remove invalid values
   
• Replace errors with blank/null, or correct the value
4. Remove the temporary column
   
• Then apply the sort again

Quick M-code example to enforce clean dates

= Table.TransformColumns(

    Source,

    {{"DateColumn", each try Date.From(_) otherwise null, type date}}

)

If the error persists, feel free to share:

• A screenshot of the error message in Power Query
• A few sample rows from the Date column

Best regards!

PS: if you find this help

[Syauqi]

I already follow your advice but error still happen on Date column. Here's the one of error massage 

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
Thu 7/24/25

[ronrsnfld]

Your sample and error message explains things. What you need to do is add the culture (locale) argument to the very first step where you set the data types (usually the second step named #"Changed Type".

In the formula bar, the code will look something like:

= Table.TransformColumnTypes(Source,{{"Start_Date", type date}, {"End_Date", type date},{"Other Columns", type ...}})

You want to add the culture argument at the very end:

= Table.TransformColumnTypes(Source,{{"Start_Date", type date}, {"End_Date", type date},{"Other Columns", type ...}}, "en-US")

Note that the culture argument reflects the culture of the source date, not your computer date culture.

If errors remain after this, it is likely that the Weekday Name is incorrect for the date. You will need to recheck your source data to decide whether the Weekday Name is correct or the date itself is correct, and solve the problem a bit differently.