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
m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

Hi Anonymous,

 

Try adding a boolean filter with something like this:

 

let
    BusID = List.Buffer( BBDD[Bus_ID] ),
    Source = BBDD,
    t = Table.FromColumns(
        Table.ToColumns( BBDD ) &
        {
            List.Transform(
                List.Zip( {BusID, List.Skip( BusID, 1)} ),
                each _{0} = _{1}
            )
        }, Table.ColumnNames( BBDD ) & { "Check" }
    )
in
    t

 

 

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

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!