Comparing two columns in the same table (uneven rows)

[Anonymous]

Hi everyone,

I am not sure if this is possible in Power BI, but I am looking to create a new column that returns "Won" or "Loss" if x amount of conditions match. 

The problem is the table is a combined dataset where one table has 57,000 records and the other has 840,000. (Most of the columns are null on the first half of the table).

Is it possible to compare them in anyway? I know creating a new column matches row-by-row and that will not work as they are not related in the lineup.

Thank you!

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

According to the scenario you mentioned, I created a simple sample, you can refer to my steps .

(1)I import two excels (data 1 and data 2) to desktop .The numbers of rows in the two excels are different .

(2)I extract a column from a table with fewer rows to create a new table .

Table = SELECTCOLUMNS('data 1',"ID",'data 1'[ID])

(3)Use LOOKUPVALUE() to extract the columns compared from two data tables into the new table .

Value 1 = LOOKUPVALUE('data 1'[Value],'data 1'[ID],'Table'[ID])

Value 2 = LOOKUPVALUE('data 2'[Value],'data 2'[ID],'Table'[ID])

(4)Create a calculated column to return the judgment value.

W-L = IF('Table'[Value 1]>'Table'[Value 2],"Won","Loss")

The final result is as shown :

Notice : The column ID in the new table I created here is extracted from the table with fewer rows, so the ID is only displayed to 10. You can come according to your own ideas .

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , you can do it, data load might slow down.

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

or

Can you share sample data and sample output in table format

[Anonymous]

Hi @amitchandak ,

The videos don't show exactly what I am looking for. I do think the lookupvalue function is what I need, but I basically need an lookupvalue function with an if condition to return a column value of "Won" or "Loss" 

Rather than return an already existing column