Count Distinct Values based on another column in Power Query

[totamum]

I need to count the number of ip_addresses for each username.  Group By doesn't seem to work as I cannot select another column when I choose Count Distinct Rows.  I've tried a number of DAX measures but they don't work either.  Any ideas anyone??  I've pasted my table below removing sensitive data.  Many thanks

[v-lgarikapat]

Hi @totamum Thanks for reaching out to the Microsoft fabric community forum.

Could you please provide sample data that fully represents the issue or question you're referring to? Kindly ensure the data is in a usable format (e.g., Excel or CSV) rather than a screenshot, and does not contain any sensitive or unrelated information.

Looking forward to your response.

Best regards,
Lakshmi.

[totamum]

Thank you for your reply.  I don't have any sample data.  I was hoping the structure and column names would be enough.

[v-lgarikapat]

Hi @totamum 

The logic has been implemented using sample data, and the PBIX file is attached for your reference. Kindly review and let me know if you have any questions or require any additional details.

Thanks,

Lakshmi

[v-lgarikapat]

With DAX

Create a new DAX measure and use it in a visual along with the required columns, as shown below.

Thanks,

lakshmi.

[totamum]

It's actually for PowerQuery, the source file is an excel file so not PowerBi. Neither of these work unfortunately.

[v-lgarikapat]

Thanks for the clarification. Since the data is being handled in Power Query (Excel) and not in Power BI, DAX-based solutions would not be applicable here.

Please use the following Power Query approach to achieve the distinct count of IP addresses per username:

1. Perform a Group By on the username column using the "All Rows" option.

2. Add a custom column with the following logic:
   Table.RowCount(Table.Distinct([All], {"ip_address"}))

This will return the distinct count of IP addresses for each user.

Please let me know if you need any further assistance.

1. Open Excel

2. Go to the Data tab in the ribbon

3. Click on:

   • Get Data → choose your source (Excel, CSV, etc.)

This will open Power Query Editor

Excel Power query 

Thanks,

Lakshmi.

[totamum · Accepted solution]

Thanks but none of these worked.  I ended up just connecting to the sheet via Close Load & To in PowerQuery and selecting Only Create Connection and Add This Data to the Data Model and then creating a Pivot Table from the Data Model with a column for usernames and IP addresses as Distinct Values.