Unable to compare two collections.

(0) Share

Report

Posted on by JustinM5

Hello,

I have two collections I wish to compare, one grabs the contents from a SharePoint list (colReturnList) and the other a Power BI query (colReturnData) received through Power Automate. The values within the PowerBI collection received from the ParseJSON are Text.

The code I'm using to compare is:

ForAll(colReturnList,

If(

IsBlank(LookUp(colReturnData, 'Asset Tag' = ThisRecord.'Asset Tag')),

Patch(colReturnList, ThisRecord, {'File Status': "Returned" })

))

It tells me that the 'Asset Tag' from the SharePoint collection is a Record and the 'Asset Tag' in the PowerBI collection is Text and cannot be compared. I saw from other posts to change the record value to 'Asset Tag'.Value, but that doesn't work. What did end up working, was creating a new collection where a column was added as text.

ClearCollect(colReturnList,
AddColumns(colReturn,
AssetText,

Text(ThisRecord.'Asset Tag')

)

)

It then allowed me to do the lookup in the original code after I changed 'Asset Tag' to AssetText.

ForAll(colReturnList,

If(

IsBlank(LookUp(colReturnData, AssetText = ThisRecord.'Asset Tag')),

Patch(colReturnList, ThisRecord, {'File Status': "Returned" })

))

I needed to do a compare on the other list, so I tried a ForAll() on colReturnData, but it doesn't work. It won't allow me to do the LookUp and this is where I'm stuck. I don't understand why it worked the one way, but it won't work when doing the LookUp in the opposite direction.

ForAll(colReturnData,

If(

IsBlank(LookUp(colReturnList, 'Asset Tag' = ThisRecord.AssetText)),

Patch(colReturnData, ThisRecord, {'File Status': "New" })

))

I tried the same workaround of adding an AssetText column to colReturnData, I was then doing a LookUp between AssetText and AssetText, but still says the records don't match.

Any help is appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Nirav Raval (Akira28) 153 Moderator on at

Like (0)

Report

Hi JustinM5 ,

May be you can try to add the column to both collection as AssestTagText to store the text of 'Asset Tag'. Then use the below code might help to resolve the issue, I made some changes as from the both Patch code. 🤞

Patch SharePoint -

ForAll(colReturnList,
If(
IsBlank(LookUp(colReturnData, AssetTagText = ThisRecord.AssetTagText)),
Patch(colReturnList, ThisRecord, {'File Status': "Returned"})
)
)

Patch PowerBI -

ForAll(colReturnData,
If(
IsBlank(LookUp(colReturnList, AssetTagText = ThisRecord.AssetTagText)),
Patch(colReturnData, ThisRecord, {'File Status': "New"})
)
)

If this post solved your issue, clicking 'Does this answer your question' will help others discover the solution and close the topic. If you found it helpful, a Like would be awesome!

Regards,
Nirav J Raval (Akira28)

Was this reply helpful? Yes No
JustinM5 30 on at

Like (0)

Report

It doesn't work unfortunately. It actually breaks the LookUp that does work when I try to add a text column to both collections. There is something about the Collection format that I don't understand that is being impacted (I'm assuming).

Was this reply helpful? Yes No
Suggested answer

JustinM5 30 on at

Like (1)

Report

I resolved it by limiting the number of columns I was bringing in from SharePoint. I then converted all the columns I brought in as Text to match what was being done with the PowerBI data.

ClearCollect(
colReturnList,
ShowColumns(
AddColumns('SharePoint List',
    AssetTagText, Text('Asset Tag'),
    EmploymeeNameText, Text('Employee Name')
    FileStatusText, Text('File Status')
),
AssetTagText,
EmployeeNameText,
FileStatusText
))

Was this reply helpful? Yes No
Nirav Raval (Akira28) 153 Moderator on at

Like (0)

Report

Try this one may be for collection 🤞 -

Collection -

Refresh(YourSharePointDataSource);
Refresh(YourPowerBIDataSource);
ClearCollect(colReturnList, AddColumns(YourSharePointDataSource, 'AssetTagText', Text('Asset Tag')));
ClearCollect(colReturnData, AddColumns(YourPowerBIDataSource, 'AssetTagText', Text('Asset Tag')));

SharePoint -

ForAll(
colReturnList,
If(
IsBlank(LookUp(colReturnData, AssetTagText = ThisRecord.AssetTagText)),
Patch(colReturnList, ThisRecord, {'File Status': "Returned"})
)
)

Power BI -

ForAll(
colReturnData,
If(
IsBlank(LookUp(colReturnList, AssetTagText = ThisRecord.AssetTagText)),
Patch(colReturnData, ThisRecord, {'File Status': "New"})
)
)

Was this reply helpful? Yes No