Imported data out of order in table view

[cademitchell36]

When I import my data from excel, the data is out of order. I put a rank column next to my dates to show this effect. Why are my rows not importing in the same order as they are in in excel? However, when in query editor, it looks the same as what it does in excel.

[v-kpoloju-msft · Accepted solution]

Hi @cademitchell36,

Thank you for reaching out to the Microsoft fabric community forum.  Thank you @parry2k, for your inputs on this issue. After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

This issue occurs because Power BI does not necessarily retain the original row order from Excel when importing data. Instead, Power BI loads the data into a table format without any inherent row order unless explicitly sorted. Power BI does not store data with an inherent order. The data may appear in a different order once loaded into a table visualization.

To maintain the original order from Excel when importing data into Power BI, follow these steps. First, ensure the Rank column is recognized as a numeric column in Power Query Editor, as text-formatted numbers may affect sorting. Next, in Report View, select the table, click on the Rank column header, and choose Sort Ascending to arrange the rows correctly.

Additionally, set a default sort order by going to the Modelling tab, selecting the Rank column, and choosing Sort by Column → Rank to enforce the correct order in visualizations.

Lastly, verify that all relevant columns, such as Date and Rank, have the correct data types in Power Query Editor, ensuring that dates are stored as Date/Time and ranks as Whole Numbers. After applying these adjustments, refresh the report to check if the imported data maintains the original Excel order.

Kindly refer to the below following links for more information:
Data types in Power Query - Power Query | Microsoft Learn
Sort one column by another column in Power BI - Power BI | Microsoft Learn

I hope this could resolve your issue, if you need any further assistance, feel free to reach out. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[parry2k]

@cademitchell36 Your data is already imported, you don't have to do anything. It was just to confirm. You can continue using this table to visualize the data.

[parry2k]

@cademitchell36 in table view, how about if you sort by rank? Also, where did you add this rank column? Is it in excel or index column in PQ?

[cademitchell36]

@parry2k It fixes it when sorted by rank, I just don't want to have to utilize that to get my data to import properly. And that column was added in excel.

[parry2k]

@cademitchell36 what is this view? Table view in the power bi desktop, you have to be more specific.

[cademitchell36]

First view was on excel, how it should be ordered, second is the table view, and the third one was query editor.

[parry2k]

@cademitchell36 not sure what we are looking here, could you provide little more details.

[cademitchell36]

@parry2k It is a table that is supposed to start with data from march 11th. For some reason when I import it, It starts with the data from the 12th. Maybe it has something to do with the fact that the data for that day is all zeros?

[cademitchell36]

My dates are all jumbled as well when you look at the column sort.