cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Remove same date in datasource

Hi All,

 

How can I remove the data with the same date and state?

Please see image below. 

Capture.JPG

 

Thank you in advance!

 

Best Regards,

Boynogs123

1 ACCEPTED SOLUTION

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

You can do that by grouping the data source by date and state, then taking the earlier of the items of the group (which would be the ones for the same day). I find it easier to understand the logic by breaking the expression in multiple parts, so here you go:

ClearCollect(
    WithDate,
    AddColumns(
        Table,
        "Date", Date(Year(Time), Month(Time), Day(Time))
    )
);
ClearCollect(
    GroupedByDateAndState,
    GroupBy(WithDate, "Date", "State", "ByDateAndState")
);
ClearCollect(
    WithItemsRemoved,
    AddColumns(
        GroupedByDateAndState,
        "MinTimePerDateAndState",
        First(Sort(ByDateAndState, Date, Ascending))
    )
);
ClearCollect(
    FinalResult,
    DropColumns(
        AddColumns(
            WithItemsRemoved,
            "AC-No.", MinTimePerDateAndState.'AC-No.',
            "No.", MinTimePerDateAndState.'No.',
            "Name", MinTimePerDateAndState.Name,
            "Time", MinTimePerDateAndState.Time
        ),
        "ByDateAndState",
        "MinTimePerDateAndState",
        "Date"
    )
)

The first part adds a new column to the table (in the new collection 'WithDate') that extracts the date from the date/time value that you have in the 'Time' column.

The second part groups the collection by date and state. That will create a new collection with a row for each unique combination of date and state.

The third part extracts the line that we want; in my logic (and in your example), I'm taking the earlier of the events from that date/state combination, by sorting the groups and taking the first element.

Finally, the fourth part extracts the elements from the new column (expanding the record into individual properties), and removes the auxiliary properties that we added to implement the logic.

The attached app shows this logic. To open it, save it locally, then go to https://create.powerapps.com, select Open -> Browse, then find the file that you saved before. Once you click the button that does the removal, you can take a look at the collections (View ->Collections) to see the result of all the intermediate steps.

Hope this helps!

View solution in original post

3 REPLIES 3
CarlosFigueira
Power Apps
Power Apps

You can do that by grouping the data source by date and state, then taking the earlier of the items of the group (which would be the ones for the same day). I find it easier to understand the logic by breaking the expression in multiple parts, so here you go:

ClearCollect(
    WithDate,
    AddColumns(
        Table,
        "Date", Date(Year(Time), Month(Time), Day(Time))
    )
);
ClearCollect(
    GroupedByDateAndState,
    GroupBy(WithDate, "Date", "State", "ByDateAndState")
);
ClearCollect(
    WithItemsRemoved,
    AddColumns(
        GroupedByDateAndState,
        "MinTimePerDateAndState",
        First(Sort(ByDateAndState, Date, Ascending))
    )
);
ClearCollect(
    FinalResult,
    DropColumns(
        AddColumns(
            WithItemsRemoved,
            "AC-No.", MinTimePerDateAndState.'AC-No.',
            "No.", MinTimePerDateAndState.'No.',
            "Name", MinTimePerDateAndState.Name,
            "Time", MinTimePerDateAndState.Time
        ),
        "ByDateAndState",
        "MinTimePerDateAndState",
        "Date"
    )
)

The first part adds a new column to the table (in the new collection 'WithDate') that extracts the date from the date/time value that you have in the 'Time' column.

The second part groups the collection by date and state. That will create a new collection with a row for each unique combination of date and state.

The third part extracts the line that we want; in my logic (and in your example), I'm taking the earlier of the events from that date/state combination, by sorting the groups and taking the first element.

Finally, the fourth part extracts the elements from the new column (expanding the record into individual properties), and removes the auxiliary properties that we added to implement the logic.

The attached app shows this logic. To open it, save it locally, then go to https://create.powerapps.com, select Open -> Browse, then find the file that you saved before. Once you click the button that does the removal, you can take a look at the collections (View ->Collections) to see the result of all the intermediate steps.

Hope this helps!

View solution in original post

Anonymous
Not applicable

Hi @CarlosFigueira ,

 

Wow!! You solved my problem in just a 3 hours. I am looking to solve that problem almost a month. 

 

Thank you!!!

 

Best Regards,

 

Boynogs123

Anonymous
Not applicable

Hi @CarlosFigueira ,

 

How can I change this into.

Capture1.JPG

That look like this.

Capture2.JPG

Thank you in advance @CarlosFigueira 🙂 !

Best Regards,

Boynogs123

 

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (41,055)