Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
Please note there are 3 tables
table 1 - Int
table 2 -Gd
table 3 Exit
Main table is Int
result required in Int table by combininig all columns in one table against respective name.
P.s. - there is no unique columns (priamry key) in all 3 tables. Below table is consolidated in MS Excel, however in reality there are 3 tables.
Int username | Int email id | Gd Name | UserPrincipalName | Primary address | Exit Name | Exit email id |
Akash Rana | akash.rana$lasa | Rana Akash | b7ded$login.lasa | akash.rana$lasa | Rana Akash | |
mumtaz.devi$lasa | Devi Mumtaz | Devi Mumtaz | mumtaz.devi$lasa |
Solved! Go to Solution.
Hi @Anonymous ,
Oh sorry, I thought you were combining the GD and Exit tables into the Int table.
I need to remind you that the two words on this side of your data are in reverse order, which can cause serious problems during the creation of a new table, so please standardize the order of the words:
Here is the sample data:
Create a one-to-one link between the three tables with the Name field:
Please try to use this DAX to create a calculated table:
Merge_DAX =
ADDCOLUMNS(
'Int',
"Int Email ID", 'Int'[email id],
"UserPrincipalName", RELATED(Gb[UserPrincipalName]),
"Primary address", RELATED(Gb[Primary address]),
"Exit Email ID", RELATED(Exit[email id])
)
And the final output is as below:
Or you can use Power Query:
First merge Int and Gb:
And expand table:
Then merge this table and Exit and also expand:
The final output is the same:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
DAX can't do anything to match two names in opposite order, I think you may only be able to do that using fuzzy matching in Power Query.
Int:
Gb:
Exit:
As before, we begin by merging table Int and table Gb.
And in the Fuzzy matching options:
The number depends on the length of the name with the longest string length in the table. This number needs to be greater than or equal to this length.
And then merge Exit, the same as before:
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You mention DAX in the title, but this is the Power Query forum. And based on your description it seems that Power Query would make it easier to fulfill your needs.
Please check whether this is what you want?
Merge Table Gb and Table Exit to get Table Int:
And the final output of Table Int is as below:
If this is not the result you are looking for, could you please provide your data in a clearer format such as a screenshot in excel, the format of the sample data you are currently providing is a bit confusing I can't clearly understand what you are looking for.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-junyant-msft
Thanks for the suggestions.. However I have attached the snapshot for the query and the result. Could you please advise or steps to get the desired results.
thanks in advance.
Hi @Anonymous ,
Oh sorry, I thought you were combining the GD and Exit tables into the Int table.
I need to remind you that the two words on this side of your data are in reverse order, which can cause serious problems during the creation of a new table, so please standardize the order of the words:
Here is the sample data:
Create a one-to-one link between the three tables with the Name field:
Please try to use this DAX to create a calculated table:
Merge_DAX =
ADDCOLUMNS(
'Int',
"Int Email ID", 'Int'[email id],
"UserPrincipalName", RELATED(Gb[UserPrincipalName]),
"Primary address", RELATED(Gb[Primary address]),
"Exit Email ID", RELATED(Exit[email id])
)
And the final output is as below:
Or you can use Power Query:
First merge Int and Gb:
And expand table:
Then merge this table and Exit and also expand:
The final output is the same:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for the steps.. however my only concern is how to match the names in all 3 tables.. The data is huge in volume and the Int table has reverse name of the employees compare to GD and Exit table
Hi @Anonymous ,
DAX can't do anything to match two names in opposite order, I think you may only be able to do that using fuzzy matching in Power Query.
Int:
Gb:
Exit:
As before, we begin by merging table Int and table Gb.
And in the Fuzzy matching options:
The number depends on the length of the name with the longest string length in the table. This number needs to be greater than or equal to this length.
And then merge Exit, the same as before:
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
61 | |
23 | |
18 | |
12 |