Forum Discussion

Lukas_317's avatar
Lukas_317
Regular Visitor
4 years ago
Solved

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? 

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    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"

     

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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"