cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shamrox
Helper I
Helper I

Using LEFT and Groupby for Chart

Is it possible to take a collection or straight from the datasource, run a LEFT function on a column to grab only the first 5 characters and then groupby that resulting string to be graphed?

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

Hey there @Shamrox im going to use a collection colStrings as the datasource and add some departments to it.

and this is what is displayed

rubin_boer_0-1631817207710.png

Now i am going to add a column that gives me the first 3 characters

AddColumns(colStrings,"Short name",Left(dpt,3)) should do that and i named this column "Short name"

And this is what is displayed

rubin_boer_1-1631817330627.png

 

We want the above grouped so lets add a GroupBy

The grouped departments are called Depts and then i add a total columns by getting how many rows (countrows) are there of a department. I am keeping the original collection as we building on it.

AddColumns(
    GroupBy(
        AddColumns(
            colStrings,
            "Short name",
            Left(
                dpt,
                3
            )
        ),
        "Short name",
        "Depts"
    ),
    "Total depts",
    CountRows(Depts)
)

 

The Short name will give the name of the group and Total Depts how many there are.

rubin_boer_2-1631817541863.png

 

Now that you have the data as you want you can create a graph

rubin_boer_3-1631817570181.png

 

Hope this helps

 

 

 

View solution in original post

4 REPLIES 4
rubin_boer
Super User
Super User

@Shamrox yes it is. Use AddColumns and add the left string as a new column and then GroupBy that column.

 

Hope it helps you on a direction

Ok, made an attempt, but something is off. 

GroupBy(
    AddColumns(Left(Department,10),
     "Dept",
        Filter('Agency - Self Report', Date >= Dropdown1.Selected.StartDate && Date <= Dropdown1.Selected.EndDate),
        "Date",
        "DATA1"),
        "Count",
        CountRows(DATA1))
rubin_boer
Super User
Super User

Hey there @Shamrox im going to use a collection colStrings as the datasource and add some departments to it.

and this is what is displayed

rubin_boer_0-1631817207710.png

Now i am going to add a column that gives me the first 3 characters

AddColumns(colStrings,"Short name",Left(dpt,3)) should do that and i named this column "Short name"

And this is what is displayed

rubin_boer_1-1631817330627.png

 

We want the above grouped so lets add a GroupBy

The grouped departments are called Depts and then i add a total columns by getting how many rows (countrows) are there of a department. I am keeping the original collection as we building on it.

AddColumns(
    GroupBy(
        AddColumns(
            colStrings,
            "Short name",
            Left(
                dpt,
                3
            )
        ),
        "Short name",
        "Depts"
    ),
    "Total depts",
    CountRows(Depts)
)

 

The Short name will give the name of the group and Total Depts how many there are.

rubin_boer_2-1631817541863.png

 

Now that you have the data as you want you can create a graph

rubin_boer_3-1631817570181.png

 

Hope this helps

 

 

 

View solution in original post

This was such a perfect explanation. I appreciate the time to lay it out so simply. Thank you. 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,685)