Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.