Need help with Substitute, pathlength, and generateseries to lookup values in another table

Need help using the SUBSTITUTE function to get the PATHLENGTH and GENERATESERIES to GENERATE a virtual table that is used to filter other tables within measures.

I need to reconcile assets among multiple tables based off of some computer properties such as computer name, asset id, serial number, mac address and/or ip address.

I start with these variables from the originating table that I need to see if any of those fields match in another table. In the example below, I have added the SUBSTITUTE function for those fields where I need to lookup the values separated by the delimiter UNICHAR(10) in another table.

The first problem that I am running into is if there is a blank in any of the Table1[Lookup] columns, the Pathlength will return a 1 even if it is blank. Do I make the _pathlengths a conditional statement? For example: _pathlength = IF(_macaddress = "", "0", PATHLENGTH(_macaddress)) which will result in a 0 instead of a 1. If I do this, it generates an error with the GENERATESERIES function "The arguments in GenerateSeries function must be of a numeric or date/time type." If I leave _pathlength = PATHLENGTH(_macaddress) then I get an error with the GENERATESERIES function "The arguments in GenerateSeries function cannot be blank.

I was using the following for those fields that only had one value in the variable to compare the OS for the asset.

OS Lookup (Table1) =
var _computername = SELECTEDVALUE(Table1[Computer Name])
var _AssetID = SUBSTITUTE(SELECTEDVALUE(Table1[AssetID Lookup]), UNICHAR(10), "|")
var _AssetIDpathlength = PATHLENGTH(_AssetID)
var _serialnumber = SUBSTITUTE(SELECTEDVALUE(Table1[Serial Number Lookup]), UNICHAR(10), "|")
var _serialnumberpathlength = PATHLENGTH(_serialnumber)
var _macaddress = SUBSTITUTE(SELECTEDVALUE(Table1[MAC Address Lookup]), UNICHAR(10), "|")
var _macaddresspathlength = PATHLENGTH(_macaddress)

//table 2 computer name lookup
var _table2computername = COUNTROWS(FILTER(Table2,Table2[Computer Name] = _computername))
var _table2computernamereturned =
IF(_computername = BLANK(), BLANK(),
IF(_table2computername <> BLANK(),
CONCATENATEX(FILTER(Table2,
Table2[Computer Name] = _computername), Table2[os], UNICHAR(10))))

//table 3 computer name lookup
var _table3computername = COUNTROWS(FILTER(Table3,Table3[Computer Name] = _computername))
var _table3computernamereturned =
IF(_computername = BLANK(), BLANK(),
IF(_table3computername <> BLANK(),
CONCATENATEX(FILTER(Table3,
Table3[Computer Name] = _computername), Table3[os], UNICHAR(10))))

//table
var _table =
FILTER(
{
_table2computernamereturned,
_table3computernamereturned
},
NOT ISBLANK([Value])
)

var _filtertable =
CONCATENATEX(DISTINCT(_table),[Value],UNICHAR(10))

RETURN
_filtertable

Because concatenatex returns a string, the distinct function was not working and the same value was being returned multiple times.

I then changed the code to reflect this:

OS Lookup (Table1) =
var _computername = SELECTEDVALUE(Table1[Computer Name])
var _AssetID = SUBSTITUTE(SELECTEDVALUE(Table1[AssetID Lookup]), UNICHAR(10), "|")
var _AssetIDpathlength = PATHLENGTH(_AssetID)
var _serialnumber = SUBSTITUTE(SELECTEDVALUE(Table1[Serial Number Lookup]), UNICHAR(10), "|")
var _serialnumberpathlength = PATHLENGTH(_serialnumber)
var _macaddress = SUBSTITUTE(SELECTEDVALUE(Table1[MAC Address Lookup]), UNICHAR(10), "|")
var _macaddresspathlength = PATHLENGTH(_macaddress)
var _ipaddress = SELECTEDVALUE(Table1[IP Address Lookup])

//table 2 computer name lookup
var _table2computername = FILTER(Table2,Table2[Computer Name] = _computername)

//table 3 computer name lookup
var _table3computername = FILTER(Table3,Table3[Computer Name] = _computername)

//table
var _table =
UNION(
SELECTCOLUMNS(_table2computername, "OS", Table2[OS]),
SELECTCOLUMNS(_table3computername, "OS", Table2[OS]),
)
var _distinct = DISTINCT(_table)
RETURN
CONCATENATEX(_distinct, [OS], UNICHAR(10))

This works great until I get to a lookup field that is split by a delimiter, especially those with blank values. I tried this

//Table 2 mac address lookup
var _table2macaddressreturned =
IF(SELECTEDVALUE(Table1[MAC Address Lookup]) <> BLANK(),
VAR _txt = VALUES(Table1[MAC Address Lookup])
var _txtPath = SUBSTITUTE(_txt, UNICHAR(10), "|")
var _txtPathlen = PATHLENGTH(_txtPath)
var _pathIndexes = GENERATESERIES(1, _txtPathlen, 1)
var _pathIndexesLookups =
GENERATE(_pathIndexes,
CALCULATETABLE(
VALUES(C_Table2macs[OS]),
TREATAS( { PATHITEM(_txtPath, NOT ISBLANK([Value]), TEXT) }, C_Table2macs[MAC Address])
)
)
RETURN
CONCATENATEX(_pathIndexesLookups, C_Table2macs[OS], UNICHAR(10)))

//Table 3 mac address lookup
var _table3macaddressreturned =
IF(SELECTEDVALUE(Table1[MAC Address Lookup]) <> BLANK(),
VAR _txt = VALUES(Table1[MAC Address Lookup])
var _txtPath = SUBSTITUTE(_txt, UNICHAR(10), "|")
var _txtPathlen = PATHLENGTH(_txtPath)
var _pathIndexes = GENERATESERIES(1, _txtPathlen, 1)
var _pathIndexesLookups =
GENERATE(_pathIndexes,
CALCULATETABLE(
VALUES(C_Table3macs[OS]),
TREATAS( { PATHITEM(_txtPath, NOT ISBLANK([Value]), TEXT) }, C_Table3macs[MAC Address])
)
)
RETURN
CONCATENATEX(_pathIndexesLookups, C_Table3macs[OS], UNICHAR(10)))

//table
var _table =
FILTER(
{
_table2computernamereturned,
_table3computernamereturned
},
NOT ISBLANK([Value])
)

var _filtertable =
CONCATENATEX(DISTINCT(_table),[Value],UNICHAR(10))

RETURN
_filtertable

This works, but again it is returning the same OS for each of the PathItems so I think I need to create a virtual table with the OS that is returned from the mac addresses and then filter out the distinct values similar to what was done with the computer name lookup. The other thing I would need to do is combine both the computer name lookups and the mac address lookups into the same measure and only return distinct values and that is where I am completely stuck. I tried

OS Lookup (Table1) =
var _computername = SELECTEDVALUE(Table1[Computer Name])
var _AssetID = SUBSTITUTE(SELECTEDVALUE(Table1[AssetID Lookup]), UNICHAR(10), "|")
var _AssetIDpathlength = PATHLENGTH(_AssetID)
var _serialnumber = SUBSTITUTE(SELECTEDVALUE(Table1[Serial Number Lookup]), UNICHAR(10), "|")
var _serialnumberpathlength = PATHLENGTH(_serialnumber)
var _macaddress = SUBSTITUTE(SELECTEDVALUE(Table1[MAC Address Lookup]), UNICHAR(10), "|")
var _macaddresspathlength = PATHLENGTH(_macaddress)
var _ipaddress = SELECTEDVALUE(Table1[IP Address Lookup])

//table 2 computer name lookup
var _table2computername = FILTER(Table2,Table2[Computer Name] = _computername)

//table 3 computer name lookup
var _table3computername = FILTER(Table3,Table3[Computer Name] = _computername)

//table 2 mac address lookup
var _table2mac address =
FILTER(C_Table2macs, C_Table2macs[MAC Address] =
GENERATE(_macaddressgenerateseries,
CALCULATETABLE(
VALUES(C_Table2macs[OS]),
TREATAS({PATHITEM(_macaddresssubstitute, NOT ISBLANK([Value]),TEXT)}, C_Table2macs[MAC Address])
)
)
)

//table3 mac address will do the same as _table2macaddress

//table
var _table =
SELECTCOLUMNS(_table2computername, "OS", Table2[OS]),
SELECTCOLUMNS(_table3computername, "OS", Table2[OS]),
SELECTCOLUMNS(_table2macaddress, "os", _macaddressgenerateseries)
)
var _distinct = DISTINCT(_table)
RETURN
CONCATENATEX(_distinct, [OS], UNICHAR(10))