Error with calculated columns after adding calculated rows with Power Query

[dsj8wksnnckk]

Hi,

I have used the Power Query editor to add calculated rows to my database. The power query formula adds a value for every column so no column is left empty.

After loading the data model, I have created a calculated column (in the same table) that uses the value from column A and column B, and with LOOKUPVALUE imports values to new column C from a different table.
This formula works perfectly for values from column A and column B that are existing in the original database (original rows), but it doesn't work for the values that are created with the Power Query formula (added rows) (even if the values are THE SAME as the existing ones in other rows).

I have checked for any typo, even used c/p to ensure the validity of the values, it still doesn't work.

Any idea why this happens?

Thanks in advance,
J

[dsj8wksnnckk · Accepted solution]

I figured out my mistake. Since there are 300+ R's, and over 500k records, I simply presumed that all R's have a record with "P3 & T3" in Table2, but they don't. I simply fixed with this adding an IF statement
Formula =
IF(Table1[Project] = "P3", "T3", LOOKUPVALUE (Table2[Type],
Table2[Project], Table1[Project],
Table2[Resource], Table1[Resource]))

This resolved my issue.

[HotChilli]

There is no reason that LOOKUPVALUE treats data values which have been added in Power Query as any different to data values which have been imported.  The obvious conclusion is that the data values are different.  This is possibly because of spaces or hidden characters.

--

I suggest testing with only one set of matching columns in the LOOKUPVALUE code (to isolate the issue) and using Trim/Clean on the columns in Power Query

[dsj8wksnnckk]

Exactly. Like I said in my intial post: I already did all of that to avoid any typos (literally cut and copied all the values).

I will test it once again. If you come up with any random idea why this would be happening, please let me know.

[dsj8wksnnckk]

Original Table1:

Table1 complemented using Power Query:

Table2:

Table1 after LOOKUPVALUE:

Formula: 
Type = LOOKUPVALUE (Table2[Type],
Table2[Project], Table1[Project],
Table2[Resource], Table1[Resource])

[HotChilli]

To summarise : LOOKUPVALUE is not finding a value for some rows in a table.

Please show the LOOKUPVALUE code and provide some sample data or pictures that show the problem