cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RonHappel
Level 8

Restructure a collection

Hi,

 

I've got a collection with the following structure:

Every record has a sequence number (Nr)

There are heading records and detail records in the collection.

The heading record is uniquely identified with the columns Achternaam and Voornaam.

I would like to add the Achternaam and Voornaam to the detail records following the heading record, and create a new collection.

I can’t find a solution. Any ideas how to do this?

The picture shows a sample of the collection:

 

Knipsel.PNG

 
2 ACCEPTED SOLUTIONS

Accepted Solutions
RonHappel
Level 8

Re: Restructure a collection

I think I understand how it works, but I don't get the right result.

 

My code to transform the collection is:

//Stores the value to fill-in blanks with
ClearCollect(fillBlanksWith;{Achternaam: "";Voornaam: ""});;

//HulpTabelAfbNr to hold the solution. HulpTabelAfbNrSource is the source
ClearCollect(HulpTabelAfbNr;HulpTabelAfbNrSource);;
ForAll(
    HulpTabelAfbNrSource;
    If(IsBlank(HulpTabelAfbNrSource[@Achternaam]);
        Patch(
            HulpTabelAfbNr;
            LookUp(HulpTabelAfbNr;Nr = HulpTabelAfbNrSource[@Nr]);
            {Achternaam: First(fillBlanksWith).Achternaam;Voornaam: First(fillBlanksWith).Voornaam}
        );
        Patch(
            fillBlanksWith;
            First(fillBlanksWith);
            {
                Achternaam: HulpTabelAfbNrSource[@Achternaam];
                Voornaam: HulpTabelAfbNrSource[@Voornaam]
            }
        )
    )
);;

The solution collection and the source collection look the same., which is the old format.

The collection 'fillBlanksWith' looks okay. It holds the last Achternaam and Voornaam at the completion of the ForAll.

 

What am I doing wrong?

View solution in original post

RonHappel
Level 8

Re: Restructure a collection

HI,

 

I got it to work.

Somehow the Achternaam field isn't completely empty or the issue has something to do with was described in:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Unable-to-Patch-Empty-Fields-in-Col...

 

I added before your solution starts:

UpdateIf(HulpTabelAfbNrSource;Len(Result)=10;{Achternaam:Blank();Voornaam:Blank()});;

This updates all detail records and leaves the heading record alone.

I tried 'Result<>Blank()'in stead of 'Len(Result)=10', but that didn't work either. I know that Result always has 10 characters.

 

I left your solution alone however I think it's possible to change it whitout having to do the UpdateIf.

It's a small table so I don't want to spend more time on it.

Thanks for your help, because I never would have thought about your solution.

 

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Restructure a collection

@RonHappel 

My solution will "fill-down" the values for Achternaam and Voornaam based on the most recent non-blank value.  For example any blanks in the Achternaam column will be replaced with will Adlouni until row 4 where they will be replaced by Al Kafri.

 

 

//your original collection
ClearCollect(
    your_collection_name,
    {Achternaam:"Adlouni", Nr:1, Result:Blank(), Voornaam:"Junaid"},
    {Achternaam:Blank(), Nr:2, Result:1576450800, Voornaam:Blank()},
    {Achternaam:Blank(), Nr:3, Result:1576537200, Voornaam:Blank()},
    {Achternaam:"Al Kafri", Nr:4, Result:Blank(), Voornaam:"Abode"},
    {Achternaam:Blank(), Nr:5, Result:1576450800, Voornaam:Blank()}
);

//stores the value to fill-in blanks with
ClearCollect(fillBlanksWith,{Achternaam:"", Voornaam:""});

//your new collection to hold the solution
ClearCollect(solution_collection,your_collection_name);
ForAll(
    your_collection_name,
    If(IsBlank(your_collection_name[@Achternaam]),
        Patch(
            solution_collection, 
            LookUp(solution_collection,Nr=your_collection_name[@Nr]),
            {
                Achternaam: First(fillBlanksWith).Achternaam,
                Voornaam: First(fillBlanksWith).Voornaam
            }
        ),
        Patch(
            fillBlanksWith,
            First(fillBlanksWith),
            {
                Achternaam: your_collection_name[@Achternaam],
                Voornaam: your_collection_name[@Voornaam]
            }
        )
    )
);

 

 

The resulting collection will look like this:

 

Achternaam Nr Result Voornaam
Adlouni 1   Junaid
Adlouni 2 1576450800 Junaid
Adlouni 3 1576537200 Junaid
Adlouni 4   Abode
Adlouni 5 1576450800 Abode

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

RonHappel
Level 8

Re: Restructure a collection

Thanks,

 

I don’t see it right now, but I will test it tomorrow and let you know.

 

RonHappel
Level 8

Re: Restructure a collection

I think I understand how it works, but I don't get the right result.

 

My code to transform the collection is:

//Stores the value to fill-in blanks with
ClearCollect(fillBlanksWith;{Achternaam: "";Voornaam: ""});;

//HulpTabelAfbNr to hold the solution. HulpTabelAfbNrSource is the source
ClearCollect(HulpTabelAfbNr;HulpTabelAfbNrSource);;
ForAll(
    HulpTabelAfbNrSource;
    If(IsBlank(HulpTabelAfbNrSource[@Achternaam]);
        Patch(
            HulpTabelAfbNr;
            LookUp(HulpTabelAfbNr;Nr = HulpTabelAfbNrSource[@Nr]);
            {Achternaam: First(fillBlanksWith).Achternaam;Voornaam: First(fillBlanksWith).Voornaam}
        );
        Patch(
            fillBlanksWith;
            First(fillBlanksWith);
            {
                Achternaam: HulpTabelAfbNrSource[@Achternaam];
                Voornaam: HulpTabelAfbNrSource[@Voornaam]
            }
        )
    )
);;

The solution collection and the source collection look the same., which is the old format.

The collection 'fillBlanksWith' looks okay. It holds the last Achternaam and Voornaam at the completion of the ForAll.

 

What am I doing wrong?

View solution in original post

Super User
Super User

Re: Restructure a collection

@RonHappel 

The interesting thing about your latest post was you said Achternaam and Voornaam were being loaded into the fillWithBlanks collection correctly but they were not used to fill any fields.

 

I wonder if this section of my code is the problem.

If(IsBlank(HulpTabelAfbNrSource[@Achternaam])

 

My collection has blank fields in Achternaam and Voornaam but perhaps yours are not truly blanks.  Instead they might have a zero length text string instead.  Try this code.

If(HulpTabelAfbNrSource[@Achternaam]=""

 

If my suggestion does not work, please answer the following questions:

  1. What is your original datasource used to load data into the collection?
  2. What is the column type of Achternaam and Voornaam?
  3. Any ideas what the "blank" spaces are filled with if not Blank() or ""?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

RonHappel
Level 8

Re: Restructure a collection

Hi,

 

I've tried 

If(HulpTabelAfbNrSource[@Achternaam]=""

The same negative result.

The orginal datasource used to create the source collection is a SharePoint List.

Achternaam en Voornaam are both single line text colums with a lenghts of 25 characters. 

I don't see what's in the fileds of the source collection, Blank, spaces, nothing or something else.

This is what a Datatable control shows in powerapps (it's a different example):

Knipsel2.PNG

 

Super User
Super User

Re: Restructure a collection

@RonHappel
What data type is the Nr field?

Something weird is going on here... I made sure to fully test my code before posting.
RonHappel
Level 8

Re: Restructure a collection

The number field is added previously to the source collection with the following code:

Clear(HulpTabelAfbNrSource);;
ForAll(HulpTabelAfb;
    Collect(HulpTabelAfbNrSource;
        Last(FirstN(AddColumns(HulpTabelAfb;"Nr";CountRows(HulpTabelAfbNrSource)+1);CountRows(HulpTabelAfbNrSource)+1))));;

The Nr is used to join two tables later on.

 

RonHappel
Level 8

Re: Restructure a collection

I've created your solution in a small test app, and it works.

Raises even more questionmarks.

RonHappel
Level 8

Re: Restructure a collection

Sorry. I must add that I've created the sample collection using a clearcollect after pressing a button. So it's not physically the samen collection as used in the original app.

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,089)