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

Comparing values from two columns and write result in third one

Hello,

I have two columns. I want to compare values from second one and if there is a value which is not present in the first column, write value "true" into new column. I know these are basic questions, I am doing a favour for my friend and it seems this task is too much for a beginner like me.

 

Clipboard02.jpg

1 ACCEPTED SOLUTION

In the query-editor (!) you can add a column with this formula:

 

List.Contains(NameOfThePreviousStep[ID1], [ID2])

 

 

This will check, if the value of the current row from column "ID2" matches any occurances within column "ID1". In order to search the whole column "ID1", you need to prefix it with the name of the previous step in your query.

 

This is a sample code, which demonstrates it if you paste it into the advanced editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t]),
    ChgType = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"ID2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(ChgType, "Exists", each List.Contains(ChgType[ID1], [ID2]))
in
    #"Added Custom"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

8 REPLIES 8
v-sihou-msft
Employee
Employee

@skype_see4d

 

In this scenario, you just need to create a calculated column like:

 

Column = IF(Table[ID1]=Table[ID2],BLANK(),"True")

Regards,

 

@v-sihou-msft

 

Thank you for your reply. I think we have a misunderstanding here. I am not looking for expression to compare values in the same row, this is pretty straight forward and I can do that.

 

I am looking for expression which would return "true" only for those ID2 values, which are not present in column 1 (ID2).

 

Best regards

Matt

 

 

In the query-editor (!) you can add a column with this formula:

 

List.Contains(NameOfThePreviousStep[ID1], [ID2])

 

 

This will check, if the value of the current row from column "ID2" matches any occurances within column "ID1". In order to search the whole column "ID1", you need to prefix it with the name of the previous step in your query.

 

This is a sample code, which demonstrates it if you paste it into the advanced editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t]),
    ChgType = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"ID2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(ChgType, "Exists", each List.Contains(ChgType[ID1], [ID2]))
in
    #"Added Custom"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeFthis is a very nice lookup code.

 

You are using this code to compare two columns within same table. Is it possible to use List.Contains to lookup ID1 column in table 1 to ID2 in table 2 by any chance.

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

It would work like this:

 

List.Contains(NameOfTheQuery[ID1], [ID2])

But it might be slow on large tables.

Instead you can merge the 2 tables and instead of expanding the merged column, create a new one where you check if the merged column is empty or not: 

 

Table.IsEmpty([MergedColumn])

This will return a true/false-column.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeFthanks for your reply and actually you have guessed it correctly.

 

I am currently working with a very large dataset and Merging Queries is making the query very very slow. Because there are multiple tables with large datasets and to come the final result I am doing Merging multiple times at different steps of the query.

 

That's why I am looking for any alternative Lookup techniques in Power Query (not DAX) which may be faster than Merging.

 

I tried List.Contains on my current query and did find it somewhat quicker than Merging Tables. Your help is much appreciated.

 

Is there any other Merging/Lookup tricks you can suggest that might improve the query performance. 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

You can try setting a key in the table on the 1-side of the merge: https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-po...

 

A merge should be much faster than my lookup-formula. Maybe you want to check again with the key added.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeFthought I give you a littele feedback.

 

I tried a lot of tricks as mentioned in https://www.thebiccountant.com/speedperformance-aspects/ and https://blog.crossjoin.co.uk/?s=performance&submit=Search. But I could not improve the performance. I guess the culprit was Merging multile times to multiple tables at different steps in the final query. Each of those tables had about 25-30 steps applied for data transformation before ready for Merging. So when I tried to merge queries I guess PBI evaluated each of those tables from Step 1 before bringing that particular table to the Final Table which was why the final query became a snail.

 

The thing which helped me was reshaping my data source. All my data sources were seperate Excel Spreadsheets. Instead of importing each of those spreadsheets into PBI for data transormation and merging, I imported each of those data sets to PQWRY within each spreadsheets and did data transformation and "Close and Load" to a new table within each of the same spreadshet. E.g. Initially I used Sheet1 within Spreadsheet A. Now I used Sheet2(Containing Reshaped Data) within Spreadsheet A. So finally all my tables that were imported to PBI were only used for Merging Queries only and performance improved drastically.

 

Thanks anyway. I am an avid fan of you and Chris. Thanks for all your good work, research and contribution which helps people like me.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.