Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dsj8wksnnckk
Frequent Visitor

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 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

1 ACCEPTED SOLUTION
dsj8wksnnckk
Frequent Visitor

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.

View solution in original post

5 REPLIES 5
dsj8wksnnckk
Frequent Visitor

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
Super User
Super User

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

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
Frequent Visitor

Original Table1:

dsj8wksnnckk_0-1701859570130.png


Table1 complemented using Power Query:

dsj8wksnnckk_1-1701859596067.png


Table2:

dsj8wksnnckk_2-1701859610716.png


Table1 after LOOKUPVALUE:

dsj8wksnnckk_4-1701859732576.png

 



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

HotChilli
Super User
Super User

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors