cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lukas_317
Regular Visitor

If or other functions

Hi, evrybody

How to find positions and compare to other value, when you have two tabels on data? e.g.

Table A 

I7-8700
i7-1185G7
Athlon(tm) II X2 215
i7-1185G7
i5-8500

Tabela B

Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz
Intel(R) Core(TM) i5-8500 CPU @ 3.00GHz
11th Gen Intel(R) Core(TM) i5-1145G7 @ 2.60GHz
11th Gen Intel(R) Core(TM) i7-1185G7 @ 3.00GHz
AMD Athlon(tm) II X2 215 Processor

 

If I found values in first tabel and I compare in secend tabel, I get 1, or simply "TRUE"
It should look like this :

Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz1
Intel(R) Core(TM) i5-8500 CPU @ 3.00GHz1
11th Gen Intel(R) Core(TM) i5-1145G7 @ 2.60GHz1
11th Gen Intel(R) Core(TM) i7-1185G7 @ 3.00GHz1
AMD Athlon(tm) II X2 215 Processor1
Intel(R) Core(TM) i5-9600K CPU @ 3.70GHz0

 

It is possible? 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Table A

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jTXtTA3MFCK1YlWyjTXNTS0MHU3B/McSzJy8vM0SnI1FTw9FSKMFIwMTbEoyzTVtTAFGRALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"

 

 

Table B

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrSc3RCNJUcM4vStUI8dVUyDTXtTA3MFBwDghVcFAw1jMycPeoUorVwarWVNfCFEmtAVytoWFJhoJ7ap4CVk2Ghiam7uZALUZ6ZkRpMQdqsYBoQbbF0ddFwbEkIyc/T6MkV1PB01MhwkjByNBUIaAoPzm1uDi/CLfDLc0MDLzhLjeHmBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Compare = (c)=> List.AnyTrue( Table.AddColumn(#"Table A","Match",each Text.Contains(c,[Column1],Comparer.OrdinalIgnoreCase))[Match]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Compare([Column1]))
in
    #"Added Custom"

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Table A

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jTXtTA3MFCK1YlWyjTXNTS0MHU3B/McSzJy8vM0SnI1FTw9FSKMFIwMTbEoyzTVtTAFGRALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"

 

 

Table B

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrSc3RCNJUcM4vStUI8dVUyDTXtTA3MFBwDghVcFAw1jMycPeoUorVwarWVNfCFEmtAVytoWFJhoJ7ap4CVk2Ghiam7uZALUZ6ZkRpMQdqsYBoQbbF0ddFwbEkIyc/T6MkV1PB01MhwkjByNBUIaAoPzm1uDi/CLfDLc0MDLzhLjeHmBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Compare = (c)=> List.AnyTrue( Table.AddColumn(#"Table A","Match",each Text.Contains(c,[Column1],Comparer.OrdinalIgnoreCase))[Match]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Compare([Column1]))
in
    #"Added Custom"

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.