Forum Discussion
Comparing two columns in the same table (uneven rows)
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!
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.
3 Replies
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=czNHt7UXIe8or
Can you share sample data and sample output in table format
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
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.
