Unanswered

Sorting in Excel is somehow set

(0) Share

Report

Posted on by Brian-M

437 Super User 2024 Season 1

I have a flow that takes a PowerBI table and drops the data into a set of Excel files (row by row) based on email address in one column:

1. Looks up all the email address in the PowerBI tables.

2. Creates one Excel file for each email address.

3. Populates the rows of the PowerBI table into Excel files based on email address.

Everything works fine, but the Excel files are all sorted alphabetically by the third column. I would like to sort it by a different column but I can't figure out where the sort order is coming from.

- The Excel template I use is not set to sort by any particular column. Sorting is manual in Excel anyways so it wouldn't be set there.

-I can't find any settings in any of the PowerAutomate steps that would order the data in Excel. I don't even think that functionality exists in PA.

- I've played around with various settings on the PowerAutomate visual in PowerBI, as well as the page visuals and filters but nothing seems to work. My suspicion is that it's coming from here somehow but I just can't find it.

Any idea where Excel would be getting the command to sort by column 3??

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hello, @Brian-M

I would like you to do one thing before you do anything else if its ok. Go to a Flow Run and look at the Output of say the first 10 lines it wrote to Excel.

Was the INPUT to the Add Row already sorted.. because unless someone created a Template, which you are writing too, and they manually set the sort on that column, then you are sort of hosed, because you cannot change it from PA Cloud unless there is a script to Run.

I'd check the file that you are using and set the Sort there, and/or also validate that the data going into the Add row toa table isnt already sorted. If it is, the sort it before you loop through it and pass it to the Add row action

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Brian-M 437 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

It's not in Excel, that's clear. I checked the Power Automate "Add a Row" step and it is definitely adding them there, in that order, by the third column. But I can't figure out how or why. Why the third column? Why not the first column? So I'm guessing there's a sort order somewhere in Power BI but I can't seem to find it. Table visuals have various sorting options but the Power Automate visual does not. I tried ordering all the filter fields thinking that maybe that was also a sort order, but that didn't work.

Was this reply helpful? Yes No
Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Brian-M

So the data coming into the Add a Row is already sorted (so it sends it out that way)?

Can you clarify what you mean here?
Table visuals have various sorting options but the Power Automate visual does not.

I am not sure what Power Automate visual you mean? Do you mean the Action? or can you show me?

Unfortunately, if the data source is sorted already, there really isn't a ton that Power Automate can do, unless its a small enough dataset, that you can take the incoming data and so a sort.

1) put the sort in the excel
2) small enough dataset to sort
3) create another sorted dataset

Can I ask how many rows are there?

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Brian-M 437 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

In PowerBI, there are sorting options on table visuals:

However, the PowerAutomate visual lacks those sorting options:

That's why I'm confused about how PA grabs the data by a specific column when there isn't a way to designate which column is the sort column in the PowerAutomate visual.

Was this reply helpful? Yes No