Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Did you use Table B as your LEFT table?
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.
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.
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.
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hey Greg thanks for the reply.
left outer join will give all the rows that are common in first table.
refer above picture
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |