Forum Discussion
put value in column if record exist in other table with conditions in that table
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
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
Community ChampionI misunderstood the assignment at the beginning. I've edited my previous code above. Try it and let me know.