Forum Discussion
how to combile two tables based on condition
Hi All, i was working on one scenario where i come across a condition, where i need to join two table like this
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"
Hey ImkeF if possible provide mail id, i will forward you the pbix file to you .
Thanks in advance for help
- ImkeF
Community Champion
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"
