Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 months ago
Solved

Error when sorting rows

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 t...
Need Help
  • Royel's avatar
    Royel
    9 months ago

    Hi Anonymous 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's avatar
Mauro89
Icon for Super User rankSuper User
9 months ago

Hi Anonymous,

 

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

Anonymous's avatar
Anonymous
Not applicable
9 months ago

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's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    9 months ago

    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.