How to Convert MyCol Data

[pcakhilnadh]

I have got the data in the following format

ClearCollect(MyCol,
    {
        UserID:6,
        work_date:DateValue("20 Sep 2021"),
        time_hrs:8
    },
    {
        UserID:6,
        work_date:DateValue("21 Sep 2021"),
        time_hrs:2
    },
    {
        UserID:7,
        work_date:DateValue("21 Sep 2021"),
        time_hrs:5
    }
);

How to convert the above data to the following format

UserID	20 Sep 2021	21 Sep 2021
6	8	2
7		5

I have used a sample collection and using lookup I filled for each user but this process takes lot of time when number of columns are more ( i.e for a month ) Is there any efficiant methond to convert the data to this format in one go? 

[BCLS776]

Is there a really good reason you need separate columns for each day? That kind of data structure leads to all sorts of problems later for an app.

Instead, you can stick with the data structure you have and use LookUp() or Filter() to bring up specific records you need to use in your app.

Bryan

[v-qiaqi-msft]

Hi @pcakhilnadh,

Have you solved you problem?

Do you want to add columns dynamically based on the work date?

Could you please share a bit more about the scenario?

Actually, if you want to add columns dynamically and automatically based on the work date, I am afraid there is no direct way to achieve your need in Power Apps currently.

If you want to transfer column values from work_date as a text literal when you use the AddColumns() function, which could not work either.
Although you want to use ForAll to loop through the entire work_date column, in the hope that AddColumns can dynamically add columns to the MyCol, however, you should know that AddColumns() requires a text literal column name.

You could do nothing but the combine the LookUp() function and hard code to achieve this.

Here is a similar thread that I have responded, please check for details.

Solved: Re: PowerApps - Adding new column to a collection ... - Power Platform Community (microsoft....

Hope this could help you at some degree.

[pcakhilnadh]

If not converting the data, could you elaborate a little more on how I can show the data in UI where the date columns are added dynamically as the work_date field as more fields?

[v-qiaqi-msft]

Hi @pcakhilnadh,

Even though that you don't mention me in this reply, but I could tell you that there is no direct and efficient method to add date columns dynamically as the work_date field as more fields.

[pcakhilnadh]

What if I need to show data in UI without converting? What is the best way to show in UI?

I can apply filter on work_date and get data in the base format Table( UID, time_hrs, work_date) for a month's data. But in UI I need to show each column for each day against each user ID

My Idea

• Have a vertical gallery control for showing user ID
• Have a nested horizontal gallery control and a calendar view
• Lookup the MyCol for show time_hrs in the nested gallery control

Issue

• As lookup is taking time to load data. Suppose if I have 100 users and for each users data for the entire Month then it is taking lot of time to load the control and data. 

@v-qiaqi-msft  Could you account on my idea, if it is ok or any other ways to improve performance