put value in column if record exist in other table with conditions in that table

[ravanzaanen]

Hi Guys,

figgering out powerbi and setting up a ranking system in an existing powerbi with tables we already got. the thing is i get gaps in my ranking because players did not participate on a matchtype so it jumps from 2 to 4 and skips 3 or it starts at 2 instead of 1

we gave people based on level an ranking and filtering in the powerbi. 

Solution get another column in ranking or actualy two where we filter on matchtypeid
I already added gender and leeftijd from players and check if the age is under or equal to 18 and put an X in.
I would like to do the same with matchtypeid for lets say singleplay and doubleplay to put an x in the column when found. 

So the names are not ideal sorry.

we have the tabel #"bond_jeugd_competitie_samen ranking"
with memberid in it 

memberid	enkelranking	dubbelranking	gender	leeftijd	jeugd
1234	0.2	0.5	M	20	null
1235	0.4	1	M	18	X
1236	0.5	2	V	17	X
1237	1	2.6	V	33	null

and tabel #"bond_jeugd_competitie_samen rankinglog" i won't bother you with all the **bleep** over there. just a few columns the rest isn't important.

memberid	matchtypeid
1234	HE
1234	HD
1236	DE
1237	DD
1237	DD
1234	HD
1236	E

So the final table i'm looking for is 

memberid	enkelranking	dubbelranking	gender	leeftijd	jeugd	singleplay	doubleplay
1234	0.2	0.5	M	20	null	X	X
1235	0.4	1	M	18	X	null	null
1236	0.5	2	V	17	X	X	null
1237	1	2.6	V	33	null	null	X

so in the end we can do something like this 

rankingdubbelspeljeugdopgeslacht = VAR CurVal = [dubbelranking]
RETURN
CALCULATE(
RANKX( 'bond_jeugd_competitie_samen ranking', [dubbelranking], CurVal, ASC, Dense ),
ALLEXCEPT( 'bond_jeugd_competitie_samen ranking','bond_jeugd_competitie_samen ranking'[gender],'bond_jeugd_competitie_samen ranking'[jeugd] )
)

and just add singleplay or doubleplay in the ALLEXCEPT 

But the satement to get the X in singleplay and doubleplay is breaking my head. 
we have 3 values for singleplay "E" "HE" and "DE" and 4 for doubleplay  "HD" "DD" "GD" and just "D"

There is an relation on memberid ranking 1 rankinglog many


Hope somebody can help

[dufoq3 · Accepted solution]

Hi @ravanzaanen, 

Power Query Solution:

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTLQMwKTpkDSF4iNDIBEXmlOjlKsDliRKVgapNQQqsTQAkhEwOTN4NpBBoWB5M2R5c2hOo30zKDyxsZwK2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [memberid = _t, enkelranking = _t, dubbelranking = _t, gender = _t, leeftijd = _t, jeugd = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUfJwVYrVQfBcYDwzIM8FLmcO4rlg52HqA2qLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [memberid = _t, matchtypeid = _t]),
    MergedQueries = Table.NestedJoin(Table1, {"memberid"}, Table2, {"memberid"}, "Table2", JoinKind.LeftOuter),
    Ad_SinglePlay = Table.AddColumn(MergedQueries, "singleplay", each if List.ContainsAny([Table2][matchtypeid], {"E", "HE", "DE"}) then "X" else null, type text),
    Ad_DoublePlay = Table.AddColumn(Ad_SinglePlay, "doubleplay", each if List.ContainsAny([Table2][matchtypeid], {"HD", "DD", "GD", "D"}) then "X" else null, type text),
    RemovedColumns = Table.RemoveColumns(Ad_DoublePlay,{"Table2"})
in
    RemovedColumns

[ravanzaanen]

Thanks for the help @dufoq3  
I only not see where you put the in list and if i change to my data somehow i get less X then expected
It should be an or list so if it contains E or HE or DE then X else null for singleplay  But beside i put in the wrong forum i love to learn from this solution also. 😉   

[dufoq3]

Hi, as you can see it works with sample data. Provide new sample (data from your screenshot) and I can check.

[ravanzaanen]

Hi @dufoq3 

Its a bit hard to give a good data set i think. 
The thing is i have this database to begin with.
like 11.000 records of unique memberid's 

let
    Bron = MySQL.Database("192.168.2.110:3306", "bond_jeugd_competitie_samen", [ReturnSingleDatabase=true]),
    bond_jeugd_competitie_samen_ranking = Bron{[Schema="bond_jeugd_competitie_samen",Item="ranking"]}[Data],
    #"ranking enkelspel1" = Table.AddRankColumn(#"bond_jeugd_competitie_samen_ranking","Rankingenkel",{"enkelranking",Order.Ascending},[RankKind=RankKind.Competition]),
    Aangepast1 = Table.AddRankColumn(#"ranking enkelspel1","Rankingdubbel",{"dubbelranking",Order.Ascending},[RankKind=RankKind.Competition]),
    #"Dubbele waarden verwijderd" = Table.Distinct(Aangepast1, {"memberid"}),
    #"Query's samengevoegd" = Table.NestedJoin(#"Dubbele waarden verwijderd", {"memberid"}, #"bond_jeugd_competitie_samen players", {"memberid"}, "bond_jeugd_competitie_samen players", JoinKind.Inner),
    #"bond_jeugd_competitie_samen players uitgevouwen" = Table.ExpandTableColumn(#"Query's samengevoegd", "bond_jeugd_competitie_samen players", {"gender", "Leeftijd"}, {"gender", "Leeftijd"}),
    #"Voorwaardelijke kolom toegevoegd" = Table.AddColumn(#"bond_jeugd_competitie_samen players uitgevouwen", "jeugd", each if [Leeftijd] <= 18 then "X" else null)
in
    #"Voorwaardelijke kolom toegevoegd"

 And have rankinglog with over 200.000 records and growing ofcourse (beacuse every match played means 2 or 4 records extra in this table).

let
    Bron = MySQL.Database("192.168.2.110:3306", "bond_jeugd_competitie_samen", [ReturnSingleDatabase=true]),
    bond_jeugd_competitie_samen_rankinglog = Bron{[Schema="bond_jeugd_competitie_samen",Item="rankinglog"]}[Data]
in
    bond_jeugd_competitie_samen_rankinglog

in rankinglog memberid is multiple times in it for every match played over years 1 per match. That could be a Single match 1 vs 1 played or a double match 2 vs 2 played. 

We need to figger out if the person (memberid have 1 or more single events in the table rankinglog singelplay (matchtypeid = "E" or matchtypeid = "HE" or matchtypeid = "DE" but can also be 1 time E and next time DE or HE. 
could be 1 row could be 100 matches in it or more. that have E or DE or HE for singleplay and should give an X on the memberid when one of those 3 shows up in al your records in that table. 

Same for doubleplay but the matchtypeid for a kind of doubleplay is "D" "HD" "DD" and "GD"  it could be that you have only GD or only D or have GD and HD or GD and DD in the record. if any of these are in that table on your memberid it should give an X om the rankingtable. 

Hope the question is more clear now. 
image of part of the table rankinglog more columns do apply  

[dufoq3]

I misunderstood the assignment at the beginning. I've edited my previous code above. Try it and let me know.