Power Query Load to worksheet in Random Order

[Vaslo]

Hi,

I am rebuilding a Power Query query in Excel 2013 that didn't work very well in my version of Excel 2010 (plus I'm stuck with 32 Bit Excel at work due to our lousy financial add-ins) Power Query Question - I am using Power Query to pull in a large number of results from a much larger table that has Business unit codes, Customer Names, and Product numbers. The final step of the query has the data in the same shape exactly as Database, which has the customer names in Alphabetical order and then the product codes in a nice order as well. However, when I "Close and Load to", the order of the table is very random and I'm not sure why. I'm trying to avoid extra steps in the shaping of data if possible as the more complex queries really drag down the quality of the workbook calcs.

Any reason why this would happen? Why would Power Query just randomly spit out the results different than what I see in the initial edit? Is there a way to fix this? It never did this in my old build in 2010.

Thanks!

 

[ImkeF]

I don't know why that is, but in order to keep some sorting order you need to wrap the step into: Table.Buffer(....).
Specially when loading to the PP Data Model the sort order will be destroyed otherwise.

 

[Vaslo]

I don't know why that is, but in order to keep some sorting order you need to wrap the step into: Table.Buffer(....).
Specially when loading to the PP Data Model the sort order will be destroyed otherwise.

Thanks for the quick response!

Not sure what I should wrap. I have a simple pull:

let    Source = Access.Database(File.Contents("C:\model\MasterData-2015-08-15B.accdb")),
    _TotalTransactionData = Source{[Schema="",Item="TotalTransactionData"]}[Data],
    #"Filtered Rows" = Table.SelectRows(_TotalTransactionData, each ([Fiscal Month] <> "2016 / 04") and ([NewCode] = "3730") and ([Fiscal Year] <> 2012) and ([FillCode] <> "110LB"))
in
    #"Filtered Rows"

Do you have a suggestion where I would put that?

I think you are right - in 2010 I couldn't load to the Data Model so it just came through correctly as is. Now that I am loading to the data model, perhaps that is causing the issue.

 

[ImkeF]

See the last step here:

#"Filtered Rows" = [B]Table.Buffer([/B]Table.SelectRows(_TotalTransactionData, each ([Fiscal Month] <> "2016 / 04") and ([NewCode] = "3730") and ([Fiscal Year] <> 2012) and ([FillCode] <> "110LB"))[B])[/B]
in
    #"Filtered Rows"

If this doesn't work, you should insert a explicit sort-operation as the last step and wrap this one into the Table.Buffer instead.

 

[Vaslo]

Thanks again for the help ImkeF. I couldn't really get this to work, and the Data Model kept crashing despite not that much data. I restarted my work and am not adding these tables to the data model and the problem is no longer there.

Thumbs down on 32-bit Power BI in Excel 2013. It's been nothing but wonky problems even doing simple stuff and team leaders are not impressed with all the breaking. I even have the newest version installed. Microsoft's response is to upgrade to 64-bit, but we just can't with all the financial add-ins we have.

Thanks.

 

[ImkeF]

Sad to hear that.
Yes, Table.Buffer() means buffering it all in memory - but if - as you said - tables are not big, this is difficult to understand.