Here is one way to do it, but maybe there is a more elegant solution.
//Create collection with orignal values from SPO List. I didnt have a SPO list, so I just created manually.
ClearCollect(
colRawData,
{
City: "New York",
Vanilla: "YES",
Chocolate: "NO",
Berries: "NO",
Mint: "NO",
Lemon: "NO"
},
{
City: "New York",
Vanilla: "NO",
Chocolate: "YES",
Berries: "NO",
Mint: "NO",
Lemon: "NO"
},
{
City: "New York",
Vanilla: "NO",
Chocolate: "NO",
Berries: "YES",
Mint: "NO",
Lemon: "NO"
}
);
//Cleansed list
Clear(colCleanData);
ForAll(
Distinct(
colRawData,
City
) As city,
Collect(
colCleanData,
{
City: city.Value,
Vanilla: If(
CountRows(
Filter(
colRawData,
Vanilla = "YES" && City = city.Value
)
) > 0,
"YES",
"NO"
),
Chocolate: If(
CountRows(
Filter(
colRawData,
Chocolate = "YES" && City = city.Value
)
) > 0,
"YES",
"NO"
),
Berries: If(
CountRows(
Filter(
colRawData,
Berries = "YES" && City = city.Value
)
) > 0,
"YES",
"NO"
),
Mint: If(
CountRows(
Filter(
colRawData,
Mint = "YES" && City = city.Value
)
) > 0,
"YES",
"NO"
),
Lemon: If(
CountRows(
Filter(
colRawData,
Lemon = "YES" && City = city.Value
)
) > 0,
"YES",
"NO"
)
}
)
)
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Cheers!
Rick Hurt
