I have developed some reports in Power BI desktop for a couple of users. The users would export the underlying data from certain visuals and work on that dataset (Excel file).
The underlying data is related to procurement (spend data with supplier names and spend values in columns) and the data volume is huge (millions on lines).
When the users export underlying data into excel, they get limited set of data (due to limitation of Excel to handle near a million lines only). Due to this, a huge chunk of data doesn't get capture in the exported Excel file.
Users are okay to have limited dataset, but they want at least a data with top suppliers (means, supplier names who have highest spend) - In other words, if the exported data is sorted 'Descending' by spend values, the users will get an extract with spend values from high to low - and the data which doesn't get capture in Excel would contain only low value suppliers - which is okay to be ignored.
Now the actual issue is that, the exported data extract is always in a raw format - means we can't choose the sort order for the data extract before exporting it.
Even if I select the 'descending' sort order from visual - when I export the data, the data is always in a raw format - Means, the data is not actually sorted in Descending order. And hence, users miss a couple of lines with high spend values!
Is there a way to export underlying data from a visual by choosing the desired sort order?
Say, we choose sort the SPEND values by descending order, and the exported data should also contain the chunk of lines which are sorted descending by Spend values.
Assume, first column contains supplier names, second column contains spend values.
Whenever I export the data (using any logic), it always exports the data in a way that the resultant Excel file contains data sorted Ascending order by Supplier Names (A to Z) with their respective spend values in Acsending order.
Just as an illustratuve example, please see the attached screenhot - The exported excel file would always look like this!