Display all rows from a column from Sharepoint

[syhrh]

Hi, I want to display all rows in the Name column from the Employee list in the column chart x-axis as a table. I tried to create a collection and use it in the items on the chart but it doesn't work. How to create a table with a column name, col with all the rows of 'Name' column from Employee list?

//App.OnStart
ClearCollect(employeeName, ShowColumns(Employee,"Name"))

//Items
Table(
    {col: employeeName.Name}
)

I cannot display the items as all of them are in records. The displayName is inside of that record.

Thank you.

[BrianS]

In your code if you type a "." after employeeName.Name does PowerApps give you any suggestions of column names?

[RusselThomas]

Hi @syhrh ,

I'm not understanding what chart you want to create from this data...or how you expect the chart to look when it's doing what you want it to do - can you perhaps show or draw what the correct end result would look like?

Your Name column appears to be empty for a start...

The Items: property of the column chart requires a table, from which you then select which columns to present in which axis, where Labels = X axis, and the rest are series corresponding to other columns in the table and other elements of the chart.

I don't use charts that often, but I believe you have to have at least two columns in the Items: table for the chart elements - one of text for labels, one of numbers for series 1 values.

If you just give it a single column table, you'll end up with a bunch of name values on top of each other at 0,0...

So what would make up the rest of the table, once you have a column of names?

As an example, using this collection;

ClearCollect(employeeName, ForAll(Sequence(10), {Name: "Bob" & Value, Value: Value}))

Set Chart Items: to

employeeName

Labels to Name and Series 1 to Value....

Kind regards,

RT

[syhrh]

@RusselThomas 

This column chart is what I want to create. The x-axis is the EmployeeName from the Employee list. Each time a new employee is added to the list, it will also add to the chart. Each column represents the leave type that the employee has applied for and approved and it is based on a Leave Request list. For example, Annual Leave: 4, Emergency Leave: 1, Sick Leave: 5.

[syhrh]

@BrianS It gives an error if I put "."

[RusselThomas]

Hi @syhrh ,

Ok, now it's starting to make a little more sense 😉

So your table input into the chart needs to look something like this;

Name	LeaveType1	LeaveType2	LeaveType3
Bob	1	2	3
Sue	1	2	3
Sally	1	2	3

(You can emulate this with a quick test button that creates a collection like this;)

ClearCollect(employeeName, 
        {Name: "Bob", LeaveType1: 1, LeaveType2: 2, LeaveType3: 3},
        {Name: "Sue", LeaveType1: 1, LeaveType2: 2, LeaveType3: 3},
        {Name: "Sally", LeaveType1: 1, LeaveType2: 2, LeaveType3: 3},
        {Name: "John", LeaveType1: 1, LeaveType2: 2, LeaveType3: 3}
    )

On your chart you would then select the chart object (default name is ColumnChart1) and set the

• Items: property to employeeName,
• Labels to Name,
• Series1 to LeaveType1,
• Series2 to LeaveType2 and
• Series3 to LeaveType3

You would then set the NumberOfSeries: property to 3 or however many columns you might have - and possibly change the itemColorSet to only three colors (or as many as you have columns of data, so you can get a color per column).

This will give you something like this;

grouped column chart

For your data specifically, you are starting with a Name column - you still need to add the leavetype columns containing the number of days each employee has taken from each leave type - where is this data coming from?

If it was in the same list as the names, then just add the data columns to your ShowColumns() expression.

If it's somewhere else, then for each Name in employeeName, you need to fetch the relevant data using LookUps - which means some sort of common unique identifier between where the names are, and where the leavedata is...

Kind regards,

RT

[syhrh]

@RusselThomas 

I have an Employee list where new employees can be added and it has to reflect on the chart. So I can't manually enter each record right, because I don't know how many employees are going to be added. The only fixed values are the leave types and the total number of approved leave they have taken. This is based on the Application Record list.

[RusselThomas]

Hi @syhrh ,

So firstly, I can see you're going to have problems with PowerApps charts because of how your data is structured.

This is not because your data structure is incorrect - rather just how it would need to be manipulated in order to use the Power Apps chart in the way you want to.

You may want to consider creating your chart as a tile in a Power BI dashboard and then displaying the tile in Power Apps instead, as this would require no manipulation whatsoever.

Creating the chart in Power BI would literally take seconds once you've connected it to your 'Leave Application' list data.  You can also create relationships within Power BI between this list and your employee list if needed.

Getting the Power Apps chart to do the same thing would require a lot of data manipulation, could take a while to figure out and would also be impacted by challenges like data row limits and delegation - meaning you might struggle to get accurate data and keep your app performant at the same time.

Simply put, the chart control is very limited and very particular about the structure of the data it needs to display information.  It's only meant for fairly simply datasets and doesn't auto-aggregate or pivot like PowerBI does.

For example - you want a chart that shows total days per leave type, but your leave types are currently contained by a single column called LeaveType and your days per leave type are not yet totalled - so to achieve what you want with a Power Apps chart, your data would need to contain a column per leavetype and a column containing a total of days for that leavetype. 

So for example you have;  

Name	LeaveType	Days
Bob	Annual	2
Bob	Sick	3
John	Paternity	5
Bob	Annual	3

but what you need for what you want to display in the chart is

Name	Annual	Paternity	Sick
Bob	5	0	3
John	0	5	0

Getting from the first structure to the second structure will take a lot of filtering and aggregating using functions that may not be delegable with SharePoint, or may be subject to data row limits - meaning as your data grows these functions might not be applied your entire dataset, giving you inacurrate results. 

There may be ways to work around these challenges and issues, but few will be simple to implement and maintain - whereas a Power BI tile might take some research and practice to understand, but would be a walk in the park to implement and maintain by comparison...

Personally, I would strongly recommend you look into the Power BI tile option, and if you have questions around Power Bi specifically you can ask on the Power BI forum for best responses and there are plenty of blogs and videos if you search for "Power BI tiles in Power Apps" online. 

Kind regards,

RT

[syhrh]

@RusselThomas Thank you for your explanation and I appreciate it. I couldn't use Power Bi tiles in Power Apps because displaying it requires Pro or Premium licenses which I don't have. So, I have to make do in Power Apps which I don't know how. Do you think it is possible to create that but that is gonna be limitations like the delegation issue?

[RusselThomas]

Hi @syhrh ,

You do require a Power BI Pro license or access to a Power BI node to use a tile, but that's because that is a Power BI requirement in order to share dashboards - which you're essentially doing by placing a tile in an App - however just to be clear for anyone else reading this, there's no Power Apps premium license required for a Power BI tile, just a Power BI Pro license - which you would need to share dashboards with anyone in Power BI anyway.

But if you don't have it a PBI Pro license, then you can't use tiles, so I understand where you're coming from.

To be honest, I'd even rather use an Excel sheet with a chart connected to the list and launch the sheet from Power Apps rather than use a PowerApps chart because they are just so limited - but if you absolutely have to do it, then this is how I would approach it - just note:

As soon as your query to a source returns more data than your data row limit (default 500 rows, max 2000 rows) then these expressions will stop showing a true reflection of the data and your charts will be incomplete....

So without going into linking tables, we'll just look at using the 'Application Record' list on the basis that if a users name doesn't appear here, they we can assume they have no approved leave requests to show anyway;

Add a button, set its OnSelect: property to;

ClearCollect(colGroupedByName, GroupBy(Filter('Application Record', Status="Approved"), "Name", "groupedData"));
Clear(collectTotals);
ForAll(colGroupedByName As groupedByName,
        Collect(collectTotals, {
                Name: groupedByName.Name,
                Annual: Sum(Filter(groupedByName.groupedData, LeaveType="Annual"), 'Total Days...'),
                Sick: Sum(Filter(groupedByName.groupedData, LeaveType="Sick"), 'Total Days...'),
                Paternity: Sum(Filter(groupedByName.groupedData, LeaveType="Paternity"), 'Total Days...'),
                Hospitalization: Sum(Filter(groupedByName.groupedData, LeaveType="Hospitalization"), 'Total Days...')
        }
        )
)

So if you look at the above, you need to create a column per leave type.  Then you filter the grouped data for that name by the leave type - so just make sure the column names and the strings you're filtering by match your leave types.  I also don't know what your total days column name actually is, so replace 'Total Days...' with that column name.

Once done, set your ColumnChart1 Items: property to collectTotals, then Labels to Name, Series1 to Annual, Series2 to Sick and so on for the rest.

Good luck 😊

RT