Columns order in Power Query vs Table view - exporting to excel from power query

[gingerclaire]

Hi

• I have a huge data set (about 200,000 rows) that we used to have to manually run various fixes and finds and replaces in excel (it drove me insane).
• I have finally been allowed to access this data in order to run all of these fixes automatically in power BI and it works great except...

I need to end up with this data back in excel.

• I have tried 'copy entire table' from power query where the data is perfect - but it only copies the first 1000 rows. Is there no way to export from power query?
• I have tried 'copy table' in Table view - but the columns are in the wrong order! There are a lot of columns so I don't want to reorder them manually each time. They are in the correct order on the original excel AND in power query so I have no idea why they would be different in table view? Is there no way to reorder columns in table view?
• I don't have Dax studio.
• I also tried creating the table as a visual and exporting from there - but it is limited to 150,000 rows.

Can you think of anything to help please? This whole thing will be better when I am allowed to take the whole process into Power BI and be rid of excel - BUT they won't be letting me do that if I can't solve this simple problem...

Many thanks in advance to anyone who can help a damsel in distress!

Claire

[gingerclaire · Accepted solution]

UPDATE: I found this on a different forum which seemed to work!!

There is easy fix to enforce column order in Power Query:

1. In Power Query Editor > Disable Query Load. Close and Apply.
2. Open the Query Editor again, enable the Query Load. Refresh the Query. Then Close and Apply.
   
   Link to original is here: https://stackoverflow.com/questions/54354205/trying-to-reorder-columns-in-data-view-in-power-bi

[v-stephen-msft]

Hi @gingerclaire ,

As a workaround, you can add an index column in power query and export it in batches. For example, for the first time, the data from indexs 1 to 100,000 is filtered and then exported from table visual. The second time, the data from indexs 100001 to row 200,000 is filtered and then exported also from table visual.

Then you can append the two sets of data that have been exported.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gingerclaire]

Thanks - sorry but it only lets me export 1000 at a time (not 100,000) so i would have to do that lots of times. 😞

[AlexisOlson]

Can you copy the query from Power BI to Excel? That seems like the easiest thing.

If you need to pull from Power BI, I'd recommend the Insert Table option in Excel (announced here and here). There is a row limit but you can modify the DAX query that builds the table to remove that constraint and load as many rows as Excel supports (a bit over a 1 million).

[gingerclaire]

I still havent found a solution and it seems craxy to have to 'fix' the data after I export it (when I embarded on this to tody it up).

I have never used queries in Excel, so if you were able to provide any assistance on this I would be very grateful 🙂

[gingerclaire]

This looks great - but I dont seem to have that option in excel 😞

[Greg_Deckler]

@gingerclaire Any chance you can provide just a few sample rows of data? I would like to try to determine why the columns might be in the wrong order in Table view. 

Is using Analyze in Excel acceptable?

What about using an EVALULATE statement in DAX Query View that essentially does a SELECTCOLUMNS to return the table with the columns in the right order?

[gingerclaire]

It is confidential i'm afraid but it is just drawing from an excel where the columns are in the correct order.

[gingerclaire]

Thank you - I have entered the evaluate into the Dax area in PBI which displays the table as is. How to I get it to change the order pls?

[AlexisOlson]

@Greg_Deckler, I've see this discrepancy in order before when I add a new column in Power Query. That column usually ends up as the rightmost column when the table is loaded even if it's not in the query editor. It feels like it has some kind of column schema that it updates rather than rebuilding.

[gingerclaire]

Its so frustrating as there are no calculated columns - its just pulling form an excel that is in the correct order. All I am doing is a selection of F&R in power query.