Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Delete Rows with Criteria – applied case

Hello, I hope you can help me with the following: For context, I have a database of trips, so I have columns for bus ID, dates, times, and so on, as shown below (I'll trim the number of columns for ...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi, m_dekorte 

Thank you very much for your solution
Sorry to reply so late

As I see that solution works in that example and I guess with little data. The drawback I have is that the database has about 63000 data per month. And when carrying out the process that you propose, it takes a long time (normally each step takes a maximum of 2 minutes, and in that case it took almost 2 hours and did not finish), and since I hope to carry out the process for the data of a complete year, the amount of data will be much higher.

I have noticed that in the lower right part, where the process load size is shown (I don't know if I am mentioning it correctly), it usually ends up at 33.9MB, but when performing the step of comparing the two rows it can go up to 1.3GB.

I appreciate if with these details you know of an alternative solution

m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

Hi Anonymous 

 

Sorry to hear that didn't work for you, see if this does a better job

let
    Source = BBDD,
    GroupLocal = Table.Group( Source, {"Bus_ID"}, 
        {
            {"t", each Table.LastN( _, 1), type table [Bus_ID=nullable text, Date=nullable text, Scheduled_TimeDep=nullable text, Calculate_TimeDep=nullable text, Index=nullable text]}
        }, GroupKind.Local 
    ),
    ExpandTbl = Table.ExpandTableColumn(GroupLocal, "t", List.Skip( Table.ColumnNames( Source), 1 ))
in
    ExpandTbl

 

Returns the same result, but doesn't require an Index or list operation.

 

Ps. If this helps solve your query please mark this post as Solution, thanks!