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.
You can use the following Mquery to add specific columns and the conditions u wanna join.
Table.NestedJoin(<Previous step in the Mquery>, {"Client ID", "Personal ID","Concatenated First/DOB"}, TableB, {"Client ID", "Personal ID","Concatenated First/DOB"}, <New Table Name>, <Type of Join you require>)
Hi @SDream7,
Please try following below steps for achieving this using dax.
1. The join fields should have different names in TableA & TableB.
2. Import both tables to Power BI.
3. Do not add joins on the tables.
4. Add a New Table using below DAX Formula.
TableC = DISTINCT( union( FILTER(CROSSJOIN(TableA,TableB),TableA[Client ID]=TableB[ClientID]), FILTER(CROSSJOIN(TableA,TableB),TableA[Personal ID]=TableB[PersonalID]), FILTER(CROSSJOIN(TableA,TableB),TableA[Concatenated First/DOB]=TableB[ConcatenatedFirst/DOB]), NATURALLEFTOUTERJOIN(TableA,TableB), NATURALLEFTOUTERJOIN(TableB,TableA)))
This will give you the details you are looking for. Sample Belw:
TableA:
TableB:
TableC:
Thanks,
Rahul
As per your point#3, you have said not to add any joins between tables.
However, I get below error -
Any idea?
I got the same issue as the user above:
"No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column."
How would I fix this? What do you mean you added join based on Client ID?
Sorry, i'm new to BI 😞
Hi @SDream7,
Create a new join between TableA & TableB as below through Manage Relationship.
Let me know if you get the desired result.
Thanks,
Rahul
When I link both fields into a relationship, I can only link it as a Many to One.
Then the error I get on Table C is "The Column with the name of 'Client ID' Already Exists in Table C.
Hi @SDream7,
I think you are using same name "Client ID" in both tables. For NATURALLEFTOUTERJOIN to work properly, the "Client ID" field name should be different in both tables TableA & TableB.
Could you please share the screenshot of DAX expression you used for getting TableC?
Thanks,
Rahul
That's not easy at all.
I've prepared a function for you because if you are a beginner, you will have difficulties to follow/execute the steps. Just copy this code into the advanced editor and give the query the name "MyFunction". Then call it and fill in the 3 parameter: Reference to 1st and 2nd table and a list with column names :
This should result in a code like this: MyFunctionName(TableA, TableB, {"Client ID", "Personal ID", "Concatenated First/DOB"})
(Table1 as table, Table2 as table, ListOfFieldNames) => let Source = Table1, #"Added Index" = Table.AddIndexColumn(Source, "Index1", 0, 1), Unpivot1 = Table.Unpivot(#"Added Index", ListOfFieldNames , "Attribute", "Value"), Source2 = Table2, #"Added Index2" = Table.AddIndexColumn(Source2, "Index2", 0, 1), Unpivot2 = Table.Unpivot(#"Added Index2", ListOfFieldNames , "Attribute", "Value"), Source3 = Table.NestedJoin(Unpivot1,{"Attribute", "Value"},Unpivot2,{"Attribute", "Value"},"Table2",JoinKind.FullOuter), #"Renamed Columns" = Table.RenameColumns(Source3,{{"Attribute", "Attribute0"}, {"Value", "Value0"}}), Expand = Table.ExpandTableColumn(#"Renamed Columns", "Table2", List.Difference(Table.ColumnNames(Unpivot2), ListOfFieldNames), List.Transform(List.Difference(Table.ColumnNames(Unpivot2), ListOfFieldNames), each _&"_")), SortAndBuffer = Table.Buffer(Table.Sort(Expand,{{"Index1", Order.Descending}, {"Index2_", Order.Descending}})), #"Filtered Rows" = Table.SelectRows(SortAndBuffer, each ([Index1] <> null)), MatchesFromFirstTable = Table.Distinct(#"Filtered Rows", {"Index1"}), Custom1 = Expand, #"Sorted Rows" = Table.Buffer(Table.Sort(Custom1,{{"Index2_", Order.Descending}, {"Index1", Order.Descending}})), #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Index2_] <> null)), MatchesFromSecondTable = Table.Distinct(#"Filtered Rows1", {"Index2_"}), #"Appended Query" = Table.Combine({MatchesFromFirstTable, MatchesFromSecondTable}), #"Removed Duplicates" = Table.Distinct(#"Appended Query", {"Index1", "Index2_"}) in #"Removed Duplicates"
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
I copied the code and created the function but I keep getting this error:
An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "Client ID" to type List.
Details:
Value=Client ID
Type=Type
Did you use the curly brackets around your list of column names?
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
Hi @SDream7,
This is a tricky one believe that @ImkeF can help you, she is an M expert and always shows new ways to change our data she is a great Datanaut.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |