New column not visible in Power Query Editor

[Anonymous]

Hello Folks,

I have created a new column named "Executive Director" in Power BI desktop, in this new column, I am getting data from another table named "systemusers" using LOOKUPVALUE function - the data is coming as expected.

Now I need to do some transformation, when I open Power Query Editor - the column is not visible there - is there any limitation ? If so how I can perform the same lookup function within Power Query Editor ?

Thanks.

[mahoneypat]

DAX calculated columns are updated after the query is loaded, so you won't see those columns there.  The simplest way to do it in the query editor is to Merge the two tables on your LOOKUP column and expand just that field.  Depending on how big your data set is, FYI that adding merge steps can significantly increase refresh times (but try it and see if it is acceptable).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[parry2k]

@Anonymous if you added the column using DAX (seems like you did) , it will not show up in Power Query. You have to add the column in PQ.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k Yes, you are right - I have added the column using DAX.

I tried to perform the same steps in PQ however I could not find any pre-built function like LOOKUPValue or I am doing something wrong there ?

Can you help me there or share some resources if handy.

Thanks

[parry2k]

@Anonymous you can use merge in PQ

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k I checked the possibility of merging however the second table contains list of Users which comes from a LDAP hence a heavy one - so taking in consideration your recommendations I will keep this as a last option.

As both table contains a relationships, would it be possible to add a new column in joining both tables ?

Is this possible in PQ ? If so can you show me please.

Thanks.

[parry2k]

@Anonymous let's go back to the actual problem and explain what you are trying to do. Answer to your original question was already provided and I think it is better to look at the problem. Explain the problem with sample data and expected output and it will save time.

[Anonymous]

@parry2k Sure.

In my mainTable, I am getting the GUID of the user which has submitted the data.

The User Details (Name, Email, etc) are stored in another table named SystemUsers.

Using DAX, to get the value of these users - I have simply used the following formula which works fine.

Newcolumn = LOOKUPValue(systemusers[fullname],systemusers[ownerid],mainTable[cr6d3_companyceo])

My challenge is to do the same in PQ.

Thanks and regards.

[parry2k]

@Anonymous you don't need to do anything in PQ or lookup assuming you have a relationship between these two tables and systems users is on the one side of one to many or one to one relationship and if that is the case, add column in your data table as below

Full Name = RELATED ( systemsusers[FullName] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k As I explained above, I cannot use the column added in DAX in PQ 😞 

[parry2k]

@Anonymous but why it matters that column has to be added in PQ. This is the most effective and best practice solution. Merging and lookup are costly functions. I don't get the point that this column needs to be in PQ, what is the importance of that? Please explain?

[Anonymous]

@parry2k sure, 

1: I need to parse that column to show the user details in the reports (Full Name, Email)  

2: Afterwards, I need to transform the data to show up the Company Name as a column.

   (you and Harsh already helped me in the same yesterday: see https://community.powerbi.com/t5/Desktop/How-to-sum-columns-horizontally-and-dynamically/m-p/1187289...)

[parry2k]

@Anonymous not sure how this post is relevant to the other one. I think you are over complicating everything. Maybe it is better to share sample data in excel with the expected output.

[Anonymous]

@parry2k Then ignore the last reply.

The requirement as of now is to lookup the value in another table - as we now using DAX its simple - how to achieve the same in Power Query ? I dont think so its that complicated.

[Anonymous · Accepted solution]

Here is the way to LookupValue on Power Query Editor:

Menu Add Column > Custom Column = Table.SelectRows(systemusers, each Text.Contains([ownerid],[cr6d3_companyceo]))

credit: https://www.powerquery.io/table/table.selectrows