M language: How to lookup value from Query1 to Query2

[ngct1112]

Hi,

To simplify, there are 2 queries which contain 1 column only separately.

Is it possible I could do something like, " If Query1 contains Query2 then Query2"?

Will be fine if there are any ways to get the same result. Thanks!

Query1

ABC1, 123, 234A

CDEF2-2, 15B

DEF3-12, 128C

EFGH4-543, 121D

FGH5, 23151, 2185E

GHI6, 21654, 18F

HIJ7, -2132, 213G

IJKLM8-8561, 0H

JKL9-21, 451I

KLMNOP0, 0, 11J

Query2

ABC1

CDEF2

DEF3

EFGH4

FGH5

GHI6

HIJ7

IJKLM8

JKL9

KLMNOP0

[Geradav]

Hi @ngct1112 

You can create a custom function to evaluate and return the desired value, like this

(ValueToCompare as text) as text => let
  OutputValue = Table.SelectRows(Query2Tbl, each Text.Contains(ValueToCompare, [Col1]))
in
  OutputValue{0}[Col1]

Then you call that function in a Table.AddColumn()

Let us know if that works for you

Regards

David 

[Geradav · Accepted solution]

@ngct1112 

You can also use the following without passing by an external custom function

Table.AddColumn(#"Changed Type", "OutputValue", (ValueToCompare) as text => Table.SelectRows(
  Query2Tbl, 
  each Text.Contains(ValueToCompare[Col1], [Col1])
){0}[Col1])

Hope That is helpful 

[ngct1112]

@Geradav , I have read some acticle but still do not figure out how to add and apply the custom function. May I know do you have any resource I could learn from.

However, the second formula is really awesome and it works incredibly!!

Great thanks! 

[Geradav]

@ngct1112 

The Power Query documentation from Microsoft at https://docs.microsoft.com/en-us/powerquery-m/ is surely a good start.

Then you can read "M is for data Monkey" https://www.skillwave.training/shop/m-is-for-data-monkey/

Read more articles and subscribe to some newsletters here https://www.poweredsolutions.co/

[ngct1112]

@Geradav Appreciated with your help and advise. Will follow the path to learn!

[Mariusz]

Hi @ngct1112 

You can use Merge Queries
https://www.youtube.com/watch?v=dTdFt9AvDHA

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn