Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 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 that the date format was correct for each row by using “Using Locale,” but that didn't help. Thank you for your help.

Need Help
  • Royel's avatar
    Royel
    8 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.

6 Replies

Replies have been turned off for this discussion
  • Royel's avatar
    Royel
    Super User
    8 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
    Super User
    8 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
      8 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
        Super User
        8 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.

  • v-achippa's avatar
    v-achippa
    Community Support
    7 months ago

    Hi Anonymous,

     

    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's avatar
      v-achippa
      Community Support
      7 months ago

      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