Forum Discussion

dsj8wksnnckk's avatar
dsj8wksnnckk
Resolver I
2 years ago
Solved

Error with calculated columns after adding calculated rows with Power Query

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 h...
Need Help
  • dsj8wksnnckk's avatar
    dsj8wksnnckk
    2 years ago

    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's avatar
HotChilli
Community Champion
2 years ago

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's avatar
    dsj8wksnnckk
    Resolver I
    2 years ago

    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.