Input collection: myCities52
Country | City | Population |
United States | New York | 8600000 |
Los Angeles | 4057000 | |
Chicago | 2679044 | |
Canada | Toronto | 5429000 |
Montreal | 3519000 | |
Mexico | Mexico City | 8000000 |
Output collection: mySolution52
Country | City | Population |
United States | New York | 8600000 |
United States | Los Angeles | 4057000 |
United States | Chicago | 2679044 |
Canada | Toronto | 5429000 |
Canada | Montreal | 3519000 |
Mexico | Mexico City | 8000000 |
Solution code:
//Create a collection
ClearCollect(
myCities52,
{Country: "United States", City: "New York", Population: 8600000},
{Country: Blank(), City: "Los Angeles", Population: 4057000},
{Country: Blank(), City: "Chicago", Population: 2679044},
{Country: "Canada", City: "Toronto", Population: 5429000},
{Country: Blank(), City: "Montreal", Population: 3519000},
{Country: "Mexico", City: "Mexico City", Population: 8000000}
);
//Fill cells with last non-blank value code
ClearCollect(fillBlanksWith,{Country:""});
ClearCollect(mySolution52,myCities52);
ForAll(
myCities52,
If(IsBlank(myCities52[@Country]),
Patch(
mySolution52,
LookUp(mySolution52,City=myCities52[@City]),
{Country: First(fillBlanksWith).Country}
),
Patch(
fillBlanksWith,
First(fillBlanksWith),
{Country: myCities52[@Country]}
)
)
);