Forum Discussion

skype_see4d's avatar
skype_see4d
Frequent Visitor
9 years ago
Solved

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 quest...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    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"
smpa01's avatar
smpa01
Community Champion
8 years ago

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.