joining two tables but i also have 30 noncommon rows in the second table.

[ajaydev]

I have two tables with columns batsman,batsmanruns & bowler,wickets .I want to merge them as player,runs,wicket .But the problem is there are 30 bowlers who are not present in the batsman column.how do i merge them too into the table.What is the right method?

[parry2k]

@ajaydev Alternatively you can just create a table with a unique list of players and then set the relationship of this new table with each table, and in the table visual, you can pull players from this new table, and runs/wickets from the respective table. No need to do the merge in PQ, just create a table of players.

let
    // Create unique tables of players
    Source = Table.FromList(List.Distinct(List.Combine({Batsman[Batsman], Bowler[Bowler]}))),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Player"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Player", type text}})
in
    #"Changed Type"

[parry2k]

@ajaydev create a new blank query and add the following M expression. In this code I'm assuming tables names are Batsman and Bowler.

let
    // Create unique tables of players
    Source = Table.FromList(List.Distinct(List.Combine({Batsman[Batsman], Bowler[Bowler]}))),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Player"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Player", type text}}),
    // Merge with Batsman table to get Runs
    #"Merged Queries - Batsman" = Table.NestedJoin(#"Changed Type", {"Player"}, Batsman, {"Batsman"}, "Batsman", JoinKind.LeftOuter),
    #"Expanded Batsman" = Table.ExpandTableColumn(#"Merged Queries - Batsman", "Batsman", {"Run"}, {"Run"}),
    // Merge with Bowler table to get Wickets
    #"Merged Queries - Bowler" = Table.NestedJoin(#"Expanded Batsman", {"Player"}, Bowler, {"Bowler"}, "Bowler", JoinKind.LeftOuter),
    #"Expanded Bowler" = Table.ExpandTableColumn(#"Merged Queries - Bowler", "Bowler", {"Wickets"}, {"Wickets"})
in
    #"Expanded Bowler"