Most efficient way of sorting rows

[SjoerdV]

Dear all,

to start I'd like to say that I absolutely love PBI and I'm impressed how well people are helped with their questions on this forum. When having a question the first site I visit is this forum, and I've been reading since a long time. However, to my specific question I must admit that I haven't found a answer, which is understandable as my data is partly different from the "standard situation".

We carry out transports (called a file) with trailers. A file has a property (called FileType), where I'd like to allocate costs from specific files to other files, based on a chronological order. Especially allocating this chronological order is what I'm struggling with, and I doubt that my current solution is either the smartest of most efficient.

I currently load 2 files with lots of columns (simplified for explanation) into Power Query. From those 2 files I create multiple dim-tables and "fact"-tables, to prevent having a fact-table with >100 columns, which are then loaded in the model. In one of thos "fact"-tables I try to achieve to create a chronological order of files per trailer. 

Simplified example;

Trailer   File Number   FileType

A001    123                 A

A001    244                 A

A001    266                 B

A001    571                 C

In my model I'd like to allocate the costs made on File Number 266 and 571 to File number 244. I do this by sorting in Power Query on Trailer, and then on 2 time/date-fields. However, as I add more data over time, this specific query becomes the slowest, most memory-consuming query by far. I remember to have read a solution somewhere with virtual tables in PQ(?), but can't recall where I found it.

My questions to you (thank you for reading this far!) are;

1. Would you use the same method as I did?

2. Is it better to create this chronological order and referring to it in DAX than in PQ?

Many thanks in advance,

Sjoerd

[v-juanli-msft]

Hi @SjoerdV

I'm not clear with the following:

Which data source do you connect to? folder or excel files?

from your simplified example,i can't see any date/time column, so I wonder how do you create a chronological order of files per trailer?

For this :

allocate the costs made on File Number 266 and 571 to File number 244. I do this by sorting in Power Query on Trailer, and then on 2 time/date-fields

I can't reproduce it on my site, maybe there is misunderstanding for it.

as i understood, I can click on the Trailer column and select "sort ascending", then the same for two "time/date" columns, but how does this behaviour allocate the costs made on File Number 266 and 571 to File number 244?

Additionaly, If you sort in Data model view, you can sort one column by another column.

Best Regards

Maggie

[SjoerdV]

@v-juanli-msft Thank you very much for your reply.

Answers to your questions;

Which data source do you connect to? folder or excel files? >> Two CSV-files.

from your simplified example,i can't see any date/time column >> There are multiple time-date columns I use to sort on, just not added them to to example in order to keep it simple. 

I can click on the Trailer column and select "sort ascending", then the same for two "time/date" columns, but how does this behaviour allocate the costs made on File Number 266 and 571 to File number 244? > The result of ordering chronologically results in a table, which I then determine, after some other checks, to allocate costs made on FileType B and C to  a specific file with FileType A.