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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kebas_Leech
Helper I
Helper I

xlookup problem

Hello, 

I'm having problems getting the same results in power query or powerbi as im getting in excel.

In the original table I have 3 columns. I need to create a new column "What i want".

As you can see, the column "Value" depends on the column "Code Compare" but what I need is a new column that gets the "Value" according to the column "Code nr".

Kebas_Leech_0-1680082720833.png

 

In Excel I'm using the following code:

xlookup([@[Code nr]]; [Code compare]; [Value]; "";0)

 

I tried to use the lookupvalue function in dax but with no success.

 

Thank you.

2 ACCEPTED SOLUTIONS
jennratten
Super User
Super User

Hello - this is how you can get the result expected with Power Query:

Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type", (x)=> x[Code compare]=[Code nr] ){0}[Value])

jennratten_0-1680088430574.png

 

View solution in original post

Thanks for posting the script.  You are getting additional rows when you expand the tables in the last step, is because the previous step, in which the new column is added, is returning a table in each row, and that table has more than one row. When you expand the column of tables, each row in the original table is expanded by the number of rows that are in the table in each F_origin.  You will need to either filter the rows in each F_origin prior to expanding, like this:

Table.TransformColumns ( 
    #"Personalizado Adicionado",
    {
        {
            "F_origin",
            (t) => Table.SelectRows ( t, each t[Column1] = "Your critieria here" )
         }
    }
)

Or drill down further when you add F_origin as a new column, like this:

Table.AddColumn(#"Preenchido para Baixo", "F_origin", each Table.AddColumn(#"Preenchido para Baixo", "Custom", each Table.SelectRows(#"Preenchido para Baixo", (x)=> x[Ord. Fabrico]=[#"Ord. Fabrico (Origem)"] ){0}[Formato][ColumnName]{RowIndexBase0}))

 

 

View solution in original post

7 REPLIES 7
jennratten
Super User
Super User

Hello - this is how you can get the result expected with Power Query:

Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type", (x)=> x[Code compare]=[Code nr] ){0}[Value])

jennratten_0-1680088430574.png

 

Hello @jennratten , thank you for replying.

 

I got the result using your solution but now powerquery is duplicating the row values.

Can you please post your script?  That will help identify why the rows are being duplicated.  

Hi @jennratten ,

 

 

I created two pages, one with a table named "what i need" and the other with "what i dont need".

The second one is the powerquery attempt. 

 

Thank you

Hello - I am unable to download the file you attached.  Can you instead post your script here?  For your query table, click Advanced Editor from the ribbon in the query editor, copy the text, click the script button in the response message here and paste the script.  Thanks!

Hello @jennratten ,

 

 

 

 

 

Thanks for posting the script.  You are getting additional rows when you expand the tables in the last step, is because the previous step, in which the new column is added, is returning a table in each row, and that table has more than one row. When you expand the column of tables, each row in the original table is expanded by the number of rows that are in the table in each F_origin.  You will need to either filter the rows in each F_origin prior to expanding, like this:

Table.TransformColumns ( 
    #"Personalizado Adicionado",
    {
        {
            "F_origin",
            (t) => Table.SelectRows ( t, each t[Column1] = "Your critieria here" )
         }
    }
)

Or drill down further when you add F_origin as a new column, like this:

Table.AddColumn(#"Preenchido para Baixo", "F_origin", each Table.AddColumn(#"Preenchido para Baixo", "Custom", each Table.SelectRows(#"Preenchido para Baixo", (x)=> x[Ord. Fabrico]=[#"Ord. Fabrico (Origem)"] ){0}[Formato][ColumnName]{RowIndexBase0}))

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors