February 13, 2018 at 1:23 am #9184
- Started: 1
- Replies: 0
- Total: 1
Hi all, I’m trying to relate data from a SQL server table across to a sharpoint table.
I have a column in what I’ll call Table A, that houses unique, 8-digit license values that comes from a SQL server view. I want to use these 8 digit values to see if they are present in a column that comes from a SharePoint table (Table B) which contains long strings of values. The column in Table B is (in part) hand-entered by users and as such the data regrettably has no true enforced structure.
So for example I want to search for a value that shows in Table A as: 12345678, and see if it exists in the greater string contained in the column of Table B, which may (or may not) look like one of the following formats.
etc. You get the point. The values need not be consecutive.
I can get the following function to work, but it only returns True values if the string TableA[Licence] is exactly equal to the string TableB[LotSerialNumber], although at least I can get it to deliver results:
=CONTAINS(TableA,[License],TableB[Lot Serial Nbr])
Ideally I would like to do the following, but it obviously doesn’t work
=CONTAINS(MaterialDisposition,[License],”*”&InvAgedSCPlanningLic[Lot Serial Nbr]&”*”)
Is there a DAX function that will work here at all? My only effective option seems to be to pick Table B apartFebruary 13, 2018 at 2:44 am #9187
- Started: 9
- Replies: 2136
- Total: 2145
SEARCH or FIND could help you out.
Since the data in table B does not have much structure, it is also possible by Searching, you could have false negative SEARCH results (manual entry in B has mistyped digit(s)), but also false positive SEARCH results (for example, looking for 56781234 would return true for the third case listed above).
Since the data has so many inconsistencies, and the possibilities of invalid search results for both positive and negative SEARCH results, could it be a negotiable item with the powers-that-be in your organization to use Power Query to clean up the data in the column that was manually created (remove commas, semicolons and spaces; split on the 8th character, and un-pivot) and then use CONTAINS?
Otherwise, it looks like SEARCH or FIND would be the way to go.
You must be logged in to reply to this topic.