how to combile two tables based on condition

[ajsingh]

Hi All,

i was working on one scenario where i come across a condition, where i need to join two table like this

[ajsingh]

Hey @ImkeF i got this result,after following your code.

its better than before, since values are not repeating from first table.

is there any possiblity that i can get null in table one, where table two has similar numbers repeating.

let
    #"Sheet1 (2)" = let
    Source = Excel.Workbook(File.Contents("C:\Users\ajsingh\Downloads\Task\B.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"sno", Int64.Type}, {"c3", Int64.Type}, {"c4 ", Int64.Type}, {"c5", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "sno", "c3", "c4 ", "c5"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns",{"sno"},#"Added Index",{"sno"},"Removed Duplicates",JoinKind.LeftOuter)
    
in
    #"Reordered Columns",
    #"Merged Queries" = Table.NestedJoin(#"Sheet1 (2)",{"Index"},Sheet1,{"Index"},"Sheet1",JoinKind.LeftOuter),
    #"Expanded Sheet1" = Table.ExpandTableColumn(#"Merged Queries", "Sheet1", {"sno", "c1", "c2", "Index"}, {"Sheet1.sno", "Sheet1.c1", "Sheet1.c2", "Sheet1.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Sheet1",{"Index", "Sheet1.Index"})
in
    #"Removed Columns"

[ajsingh]

Hey @ImkeF if possible provide  mail id, i will forward you the pbix file to you . 

Thanks in advance for help 

[ImkeF]

I wouldn't be able to work with that file, as it contains a link to a local file of yours.

Next time, please provide sample data that is clean and complete. Also, please describe your problem/requirement thoroughly. My first solution provided exactly the solution to the problem you described and now you expand it. As so often, in those cases a different solution makes more sense:

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzALCMgy1kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Tbl1Column1 = _t, Tbl1Column2 = _t]),
    Tbl1Index = Table.AddIndexColumn(Table1, "Tbl1Index", 0, 1),
    Tbl1Distinct = Table.Distinct(Tbl1Index, {"Tbl1Column1"}),
    Tbl1RemovedDups = Table.NestedJoin(Tbl1Index,{"Tbl1Index"},Tbl1Distinct,{"Tbl1Index"},"Tbl1Distinct",JoinKind.LeftAnti),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsLclXitWJVjICcpwTi5DZRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Tbl2Column1 = _t, Tbl2Column2 = _t]),
    Tbl2Index = Table.AddIndexColumn(Table2, "Tbl2Index", 0, 1),
    Tbl2Distinct = Table.Distinct(Tbl2Index, {"Tbl2Column1"}),
    Tbl2RemovedDups = Table.NestedJoin(Tbl2Index,{"Tbl2Index"},Tbl2Distinct,{"Tbl2Index"},"Tbl2Distinct",JoinKind.LeftAnti),
    #"Merged Queries" = Table.NestedJoin(Tbl1Distinct,{"Tbl1Column1"},Tbl2Distinct,{"Tbl2Column1"},"Tbl2Distinct",JoinKind.LeftOuter),
    #"Expanded Tbl2Distinct" = Table.ExpandTableColumn(#"Merged Queries", "Tbl2Distinct", {"Tbl2Column1", "Tbl2Column2", "Tbl2Index"}, {"Tbl2Column1", "Tbl2Column2", "Tbl2Index"}),
    #"Appended Query" = Table.Combine({#"Expanded Tbl2Distinct", Tbl1RemovedDups, Tbl2RemovedDups}),
    #"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"Tbl1Distinct", "Tbl2Distinct"})
in
    #"Removed Columns"

[Greg_Deckler]

Perhaps a Merge query using a Left Outer join with Table B as your left table? You would then expand the column that comes back as a table.

[ajsingh]

@Greg_Deckler  no, still i am not able to achieve desiered result

[Greg_Deckler]

Did you use Table B as your LEFT table? 

[ajsingh]

yes @Greg_Deckler both the ways i tried and with full outer join also, but as i can see in result, column S_no  has 1, 1, 2,.....so on,

but i don't want that .

one should repeat only one time,  if other table has multiple same number  (in this case table A has two 1's ).

So after join table B should show  null or blank in row where one is repeating in table A.

but in my case it is again printing previous row value.

[Greg_Deckler]

Ohhhhh, I get it now. I was trying to read the green chicken scratch on the picture and apparently came away with the wrong impression of what you wanted. I thought you were saying that you were getting nulls and wanted the values repeated. OK, got it now, let me see what I can do.

[ajsingh]

hey @Greg_Deckler any lead.

[Greg_Deckler]

Take a look at this thread here:

https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/td-p/110108

@ImkeFor @MarcelBeug are probably going to be your best bets I would think.

[ImkeF]

I would probably do it like so:

1) Add an index-column to your left table as a key

2) Remove duplicates on sno

3) Merge (Left-Outer) - that will match just one row from the lookup-table

4) Merge that back to your source-table on key (with Source on the left): So the removed dups will not get a lookup-match

[ajsingh]

Hey @ImkeF thanks for the help.

till third step i did, but in 4th step, i am not able to understand, how to do it .

Some screenshots would be a great help.

[ImkeF]

Please paste this code into the advanced editor and follow each step:

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzALCMgy1kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Table1, "Index", 0, 1),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsLclXitWJVjICcpwTi5RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index1" = Table.AddIndexColumn(Table2, "Index", 0, 1),
    RemoveDups = Table.Distinct(#"Added Index", {"Column1"}),
    #"Merged Queries" = Table.NestedJoin(RemoveDups,{"Column1"},#"Added Index1",{"Column1"},"Removed Duplicates",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(#"Merged Queries", "Removed Duplicates", {"Column2"}, {"Column2.1"}),
    Custom1 = #"Added Index",
    #"Merged Queries1" = Table.NestedJoin(Custom1,{"Index"},Expand,{"Index"},"Custom1",JoinKind.LeftOuter),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Custom1", {"Column2.1"}, {"Column2.1"})
in
    #"Expanded Custom1"

The 4th move is step "Merged Queries1": There you merge the existing table ("Custom1") with a table that has been generated in an earlier step ("Expand").

I've shown how to reference an existing step in this litte screencast here: https://www.youtube.com/watch?v=looCm3cbINw

[ajsingh]

 

Hey Greg thanks for the reply.

left outer join will give all the rows that are common in first table.

refer above picture