"Export Data" option - Ability to select the sort ...

amnadeem1991 · ‎03-04-2019

Background:

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).

Data Type:

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).

Problem:

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!

Final Question:

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.

Many thanks!

Anonymous · ‎03-04-2019

Hi amnadeem1991,

I understand your problem.

Instead of apply sorting in your visual, You can do this in Power Query and apply sorting for your data and brings it in your visual.

This helps you to get the sorted data for your customers when do an export from Power BI.

Regards,

Pradeep

amnadeem1991 · ‎03-05-2019

Hi @Anonymous

Thanks for the response.

I have tried this but it didn't help.

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!