Working with data from tables that have no direct link to each other

[Baldy]

Hi,

I have 2 tables that have no direct link to each other

1 table has a Serial Number column, an IMIE Number column and a User colunm

The second table has a Serial Number column that contains a mix of  both Serial Number and IMIE Number

I need to pull the User column from table 1 into table 2

Can anyone help?

Thanks in advance

Alan

[Eric_Zhang · Accepted solution]

---

@Baldy wrote:

Hi,

I have 2 tables that have no direct link to each other

1 table has a Serial Number column, an IMIE Number column and a User colunm

The second table has a Serial Number column that contains a mix of  both Serial Number and IMIE Number

I need to pull the User column from table 1 into table 2

Can anyone help?

Thanks in advance

Alan

---

@Baldy

The best practise is split the mixed column into two and use the LOOKUPVALUE function, I guess the Serial No and IMIE No are delimited by some character like "-"?

In you case, you can still try to create a calculated column

Column =
CALCULATE (
    VALUES ( Table1[User] ),
    FILTER (
        Table1,
        SEARCH ( Table1[Serial Number], Table2[Serial Number], 1, 0 ) > 0
    )
)

Table1

Table2 with the calculated column

[prateekraina]

Hi @Baldy,

I hope you are doing this in SQL

So just join Table 2 with Table 1 based on Two conditions:

1. Serial No of Table 2 with Serial No of Table 1
2. Serial No of Table 2 with IMIE No of Table 1

This way you can pull User in Table 2.

Thannks !!

[Baldy]

Hi @prateekraina,

I dont really know SQL. I am pretty new to Power BI and currently am only familiar with the click interfaces that the system provides.

Any chane you could supply the SQL I would need based on the information suplied?

I should be able to extrapolate what i need from that.

PS thanks for the quick reply

Thanks in advance

Alan

[prateekraina]

Hi @Baldy,

Something like below will help you:

SELECT B.[SerialNo]
,A.[User]
FROM [dbo].[Table1] A
INNER JOIN [dbo].[Table2] B
ON A.[SerialNo] = B.[SerialNo]
OR A.[IMIENo] = B.[SerialNo]

It will retrieve all the users along with SerialNo (mix of Serial No and IMEI).
If you want to insert it in Table2 read about INSERT INTO in SQL.

Thanks !!!

[Baldy]

Hi

It returns the following "The syntax for 'OR' is incorrect. (DAX(B.SerialOR A.imei= B.Serial))."

This part of the statement:  "B.[Serial] OR A.[imei] = B.[Serial]" shows red zigzag lines under all but the "=" sign

Any ideas?

Alan

[prateekraina]

Hi @Baldy,

Sorry for not clarifying this earlier, what i meant was. while pulling data from database itself, you can use that SQL query so that when data in Power BI model comes in the form as you require.
Let me know if that option is not feasible for you.

Thanks !!!