Add new column to collection after split concat method

[syhrh]

Hi, I'm trying to create new columns from my collection, balancelist. I tried using the split concat method in creating the column, col but it only add 1 column, which is Result and then when I want to create another collection, col2 that references col, it doesn't work because the Result is different in col and col2

ClearCollect(col, Split(Left(Concat(balancelist, AnnualLeave &", "&CalamityLeave&", "&CarryForwardLeave&", "&EmergencyLeave&", "&HospitalizationLeave&", "&MaternityLeave&", "&PaternityLeave&", "&SickLeave&", "&QuarantineLeave&", "), 
Len(Concat(balancelist,AnnualLeave&", "&CalamityLeave&", "&CarryForwardLeave&", "&EmergencyLeave&", "&HospitalizationLeave&", "&MaternityLeave&", "&PaternityLeave&", "&SickLeave&", "&QuarantineLeave&", "))-2), ", "));

ClearCollect(col2,
Table(
    {LeaveType:"AnnualLeave", Available:CountRows(Filter(col,Result="AnnualLeave"))/CountRows(col)},
    {LeaveType:"CalamityLeave",Available:CountRows(Filter(col,Result="CalamityLeave"))/CountRows(col)},
    {LeaveType:"CarryForwardLeave",Available:CountRows(Filter(col,Result="CarryForwardLeave"))/CountRows(col)},
    {LeaveType:"EmergencyLeave",Available:CountRows(Filter(col,Result="EmergencyLeave"))/CountRows(col)},
    {LeaveType:"HospitalizationLeave",Available:CountRows(Filter(col,Result="HospitalizationLeave"))/CountRows(col)},
    {LeaveType:"MaternityLeave",Available:CountRows(Filter(col,Result="MaternityLeave"))/CountRows(col)},
    {LeaveType:"PaternityLeave",Available:CountRows(Filter(col,Result="PaternityLeave"))/CountRows(col)},
    {LeaveType:"SickLeave",Available:CountRows(Filter(col,Result="SickLeave"))/CountRows(col)},
    {LeaveType:"QuarantineLeave",Available:CountRows(Filter(col,Result="QuarantineLeave"))/CountRows(col)}
    )
    );

each row is a different user in the balancelist. 

I want to create a dataTable in Power Apps that in the items, is col2 to display LeaveType and Available based on balancelist and only current user logged in. Can anyone please help me with how to do this?

Thank you.

[Adrian_Celis]

Hi @syhrh 

Assuming that the column name of the email of the user in balancelist is named EmailColumn, then you can do something like this:

ClearCollect(col2,
Table(
    {LeaveType:"AnnualLeave", Available:LookUp(balancelist,EmailColumn=User().Email).AnnualLeave},
    {LeaveType:"CalamityLeave",Available:LookUp(balancelist,EmailColumn=User().Email).CalamityLeave},
    {LeaveType:"CarryForwardLeave",Available:LookUp(balancelist,EmailColumn=User().Email).CarryForwardLeave},
    {LeaveType:"EmergencyLeave",Available:LookUp(balancelist,EmailColumn=User().Email).EmergencyLeave},
    {LeaveType:"HospitalizationLeave",Available:LookUp(balancelist,EmailColumn=User().Email).HospitalizationLeave},
    {LeaveType:"MaternityLeave",Available:LookUp(balancelist,EmailColumn=User().Email).MaternityLeave},
    {LeaveType:"PaternityLeave",Available:LookUp(balancelist,EmailColumn=User().Email).PaternityLeave},
    {LeaveType:"SickLeave",Available:LookUp(balancelist,EmailColumn=User().Email).SickLeave},
    {LeaveType:"QuarantineLeave",Available:LookUp(balancelist,EmailColumn=User().Email).QuarantineLeave}
    )
    );

 No need to create col1

[syhrh]

@Adrian_Celis 

I don't have email column in the balancelist, only Name column with people type. I used this formula but still no values.

ClearCollect(col2,
Table(
    {LeaveType:"AnnualLeave", Available:LookUp(balancelist,Name=User().FullName).AnnualLeave},
    {LeaveType:"CalamityLeave",Available:LookUp(balancelist,Name=User().FullName).CalamityLeave},
    {LeaveType:"CarryForwardLeave",Available:LookUp(balancelist,Name=User().FullName).CarryForwardLeave},
    {LeaveType:"EmergencyLeave",Available:LookUp(balancelist,Name=User().FullName).EmergencyLeave},
    {LeaveType:"HospitalizationLeave",Available:LookUp(balancelist,Name=User().FullName).HospitalizationLeave},
    {LeaveType:"MaternityLeave",Available:LookUp(balancelist,Name=User().FullName).MaternityLeave},
    {LeaveType:"PaternityLeave",Available:LookUp(balancelist,Name=User().FullName).PaternityLeave},
    {LeaveType:"SickLeave",Available:LookUp(balancelist,Name=User().FullName).SickLeave},
    {LeaveType:"QuarantineLeave",Available:LookUp(balancelist,Name=User().FullName).QuarantineLeave}
    )
    );

[phipps0218 · Accepted solution]

The issue here is User().FullName may differ from the Author's Name in the SharePoint. In your SharePoint what is your person column called?

I would then change your lookup as suggested by @Adrian_Celis to use email something like:

{LeaveType:"AnnualLeave", Available:LookUp(balancelist,PersonFieldName.Email=User().Email).AnnualLeave},

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

[Adrian_Celis]

Hi @syhrh 

If it is a Person Column, then let's use the email of that to compare so we can avoid duplicates (If there are two or more users with the same name). Try this:

ClearCollect(col2,
Table(
    {LeaveType:"AnnualLeave", Available:LookUp(balancelist,Name.Email=User().Email).AnnualLeave},
    {LeaveType:"CalamityLeave",Available:LookUp(balancelist,Name.Email=User().Email).CalamityLeave},
    {LeaveType:"CarryForwardLeave",Available:LookUp(balancelist,Name.Email=User().Email).CarryForwardLeave},
    {LeaveType:"EmergencyLeave",Available:LookUp(balancelist,Name.Email=User().Email).EmergencyLeave},
    {LeaveType:"HospitalizationLeave",Available:LookUp(balancelist,Name.Email=User().Email).HospitalizationLeave},
    {LeaveType:"MaternityLeave",Available:LookUp(balancelist,Name.Email=User().Email).MaternityLeave},
    {LeaveType:"PaternityLeave",Available:LookUp(balancelist,Name.Email=User().Email).PaternityLeave},
    {LeaveType:"SickLeave",Available:LookUp(balancelist,Name.Email=User().Email).SickLeave},
    {LeaveType:"QuarantineLeave",Available:LookUp(balancelist,Name.Email=User().Email).QuarantineLeave}
    )
    );

[syhrh]

@phipps0218 I re-check my person's column name and use your formula and it works! Thank you.

Can I ask how to change this? To make the purple becomes white or invisible

[phipps0218]

@syhrh I am glad that worked! Please do accept the solution to help others find it quicker.

Changing that from purple, is this a Data Table or a Gallery? If Data Table, change the SelectedFill Colour.

[syhrh]

@Adrian_Celis I tried your formula as well and it works too. Thank you

[syhrh]

@phipps0218 It's a data table and it works. Thank you!