Restructure a collection

[RonHappel]

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:

[mdevaney]

@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]

Thanks,

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

[RonHappel · Accepted solution]

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?

[mdevaney]

@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]

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):

[mdevaney]

@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]

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]

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

Raises even more questionmarks.

[RonHappel]

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.