Create connection and condition between 2 tables through 1 common column

[Bullmasta]

Hello all, 

I am trying to merge 2 tables when the only connection is 1 column between them. I've tried the merge option but seems to be creating millions of combinations and that's probably a sign that its wrong. 

I have Table1 where i have 3 columns. Name, Type and Language, Included.

Each name will be only 1 type, but can have many languages. 

Name	Type	Language	Included
Name1	option1	English	Y
Name1	option1	French	N
Name1	option1	German	N
Name2	option1	English	Y
Name2	option1	French	N
Name2	option1	German	N
Name3	option2	English	Y
Name3	option2	French	N
Name3	option2	German	Y

And then i have Table2 where i have 4 columns too. Country, Language1, Language2 and Type2.

In this case, Country will appear minimum 2 times, since each country will have 2 types. And depending of the languages it can increase.

Country	Language	Language2	Type2
UK	English		Option1
UK	Enlgish		Option2
France	English		Option1
France	French	English	Option1
France	French		Option2

So in this example, UK has english with 2 options, which are same as the ones from Table1. 

And when there are 2 languages, they can have each language up to 2 options. 

So if in Table1, i chose a name, (which is assigned to a type), and then i chosee a country, i should be able to see the language that matches the option.

Name2 - France - English - Y

                             French - N

Hope it makes sense and so far, i am able to make this work. 

I made a Cardinality of Many-to-Many in PBI between tables through the column of languages, it works somehow.

Now a problem i face, is that i am not able to think of how to create a measure/column with conditions from between tables.

I want to be able to filter a name, country, and based on that, display the different language options + if its included or not. So far, i can make this happen... but, then what i struggle with, is to have in a separate visual, if its a Yes/No based on, if the Language1 is not included (N), but there is Language2 which is included (Y), then the Name will be a Yes... 

Name2 (is option1) - France (has 2 languages for option1) - English - Y

                                                                                                French - N  -> but has English as Language2, which English is Y... 

So this Name2 would be Yes. 

Name3 (is option2) - France (in this case only has 1 language for option2) - French - N -> has no Language2 option to check, 

so this Name3 would be a No.

Hope it makes sense what i try to achieve. Unsure if the way i connected tables is the best but i appreicate feedback for that too.

Thanks in advance! 

[dufoq3 · Accepted solution]

Hi @Bullmasta, you can merge tables which give you this result:

If you want to achieve something else, provide expected result based on sample data please (at least screenshot)

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0lHKLyjJzM8DsVzz0nMyizOArEilWB1sKtyKUvOSQQr8EAoUUFS4pxblJuahqDAiaIkRIUuMCNlhDFdghMMOVBVY7EBVALcDaEIsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, Language = _t, Included = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvVW0lFyzUvPySzOALIUgNi/oCQzP89QKVYHLp2Tji5tBJZ2K0rMS07FawJciVtRal5yBopa/OpQLIsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Language = _t, Language2 = _t, Type2 = _t]),
    Table2_LowerCasedOptions = Table.TransformColumns(Table2,{{"Type2", Text.Lower, type text}}),
    #"Merged Queries" = Table.NestedJoin(Table1, {"Type"}, Table2_LowerCasedOptions, {"Type2"}, "Table2_LowercasedOptions", JoinKind.LeftOuter)
in
    #"Merged Queries"

[Bullmasta]

Thanks dufoq3!

[dufoq3]

You're welcome 😉

[Bullmasta]

I'm in an environment where i am not able to upload files and share i'm afraid. 

how else can i share things if what i wrote is not helpful? 

Thanks.