Forum Discussion

Bullmasta's avatar
Bullmasta
Frequent Visitor
2 years ago
Solved

Create connection and condition between 2 tables through 1 common column

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. 

 

NameTypeLanguageIncluded
Name1option1EnglishY
Name1option1FrenchN
Name1 option1GermanN
Name2option1EnglishY
Name2option1FrenchN
Name2option1GermanN
Name3option2EnglishY
Name3option2FrenchN
Name3option2GermanY

 

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.

 

CountryLanguageLanguage2Type2
UKEnglish Option1
UKEnlgish Option2
FranceEnglish Option1
FranceFrenchEnglishOption1
FranceFrench 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! 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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"

     

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
    Bullmasta
    Frequent Visitor
    2 years ago

    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.