Create a column that identifies how many times an ID is detected in another different column

[elliswoods]

 n

[v-nmadadi-msft]

Hi @elliswoods ,

Could you please confirm if the issue has been resolved?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we may go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-nmadadi-msft]

Hi @elliswoods 

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.

[Vijay_A_Verma]

Let name of column be Data and previous step be Source

Insert a custom column and insert following formula

List.Count(List.Select(Source[Data], (x)=> x = [Data]))

[elliswoods]

n

[Vijay_A_Verma]

1. What is the name of column which contains ID?

2. Are you using Power BI Desktop or Excel? - This would help me to give instruction to create a custom column.

[elliswoods]

n

[Vijay_A_Verma]

Go to Add Column in Menu - Custom column and paste the below formula

Source must be replaced by previous step in the query. Hence, if previous step is "Changed Type", then Source must be replaced with #"Changed Type"

List.Count(List.Select(Source[Position Code], (x)=> x = [Parent Position Code]))

[elliswoods]

When adding this it just continually loads with no end - almost like it's crashing..

[v-nmadadi-msft]

Hi @elliswoods,

Can you please confirm if you have changed souce to the previous step, that may be one of the possible reasons for the suggested step not working.

Another possibility could be circular reference. This happens if the formula is referencing the current step or a step that comes after it, instead of a valid earlier step like "Source" or "Changed Type". When this happens, Power Query can’t resolve the logic and ends up in an infinite loop, which causes it to keep loading without completing. To fix this, double-check that the step name you're referencing in the formula is from a previous step in the query.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[elliswoods]

n

[Vijay_A_Verma]

To speed up the query, below is the sample.  For you important steps are from Custom1 onwards which you should incorporate in your query. So, your Custom1 = Table.Buffer(#"Trimmed Text")

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.Buffer(Source),
    l = List.Buffer(Custom1[Position Code]),
    #"Grouped Rows" = Table.Group(Custom1, {"Parent Position Code"}, {{"Count", each List.Count(List.Select(l, (x)=> x = _[Parent Position Code]{0})), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(Custom1, {"Parent Position Code"}, #"Grouped Rows", {"Parent Position Code"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"})
in
    #"Expanded Grouped Rows"

[elliswoods]

Strangely, this is still infinitely loading... 🤔

[Vijay_A_Verma]

How many records are in your table?

[elliswoods]

n

[Vijay_A_Verma]

Please post your complete code.

In this editor, press </> in the menu above and paste the code in that interface.

[elliswoods]

n

[Vijay_A_Verma · Accepted solution]

Use this sample file to see how to use the code.

[Vijay_A_Verma]

This is not the right way to do this. 
I need complete code from Advanced Editor.