How to add a calculated column in power query based on a related table

[bcoro]

I created this measure to count the total enrollment ID per client ID. I would like to do this through Power Query, but I don't fully comprehend the process/language in Power Query. 

Total Enrollments = COUNTX(RELATEDTABLE(Data),'Data'[EE UID])

The 'Client' Table has a unique ID,

The 'Data' Table has duplicate client IDs with unique enrollment IDs. 

[Anonymous]

Hi @bcoro 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

Hi @bcoro 
Thank you for reaching out microsoft fabric community forum.
I wanted to check if you had the opportunity to review the information provided by @ajaybabuinturi . Please feel free to contact us if you have any further questions. If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[ajaybabuinturi · Accepted solution]

Hi @bcoro,
I would suggest, you can follow the below steps

1. Perform "Group By" on Client ID column in the "Data Table"
   Power Query Editor > Transform > Group By >Rename the column as your wish. Results looks like below
   Data Table:
   
   Group By Result:
   
2. Merge the result into the "Client Table":
   Power Query Editor > Home > Merge Queries > Rename the column as your wish.
   Client Table:
   

   Merge Results:
   

   

If you don't want do the process, you can take the reference data table and do the same procedure.
Please let me know if you any questions.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[bcoro]

Yes, it worked.

I'm assuming I should duplicate my data table because I don't want it to remain grouped. Is there a way around that? At some point, I would like to add to this table with other information from the data table but don't want to create multiple tables.  

[ajaybabuinturi]

Hi @bcoro,

Here is the alternate workaround

1. Merge the Client Table with the Data Table based on the Client ID field. Make sure the Client Table as the first table and the Data Table as the second table.
2. Expand the merged table to get the EE UID field.
   
3. Select Client ID and Client Name then click on Group By and rename the New column name as Total Enrollments and hit the OK.
   
4. Using the above steps you will get required resuts and you can able to add new fileds to the Client Table in future.
   

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[ryan_mayu]

@bcoro 

in pq you can try to use merge query function to get a column from a related table.

https://learn.microsoft.com/en-us/power-query/merge-queries-overview?wt.mc_id=DP-MVP-5004616