How to count rows from another table in Power Query

[mike_viz_lord]

Table_A is a list of each STATE along with dimensions and measures for each state. Table_B is a list of ATTORNEY_NAME by STATE (one to many relationship). I'm trying to create a custom column in Table_A that counts the number of distinct ATTORNEY_NAME where Table_B.STATE = Table_A.STATE. Any help would be greatly appreciated. Looking for this solution in Power Query, not DAX (for other reasons).

Sample data:

Table_A

STATE	INCOME	EXPENSE
IL	6546541	3541654
NV	6546465	34635
NC	3534164	63546
KY	65496746	6346

Table_B

STATE	ATTORNEY_NAME
IL	JIM
IL	JIM
IL	BILL
IL	JOHN
IL	ADAM
NV	MIKE
NV	NANCY
NV	JILL
NC	EMMA
NC	HENRY
NC	CONSUELO
NC	TIM
KY	BECKY
KY	DAN

Expected Table_A

STATE	INCOME	EXPENSE	CUSTOM_DISTINCT_ATTORNEY_COUNT
IL	6546541	3541654	4
NV	6546465	34635	3
NC	3534164	63546	4
KY	65496746	6346	2

[shaowu459]

Could you upload some sample data with expected results, that will allow us understand you case and test PQ code more easily.

[mike_viz_lord]

Sure, just added to original post.

[shaowu459]

Please test below code:

let
    tblA = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    tblB = Excel.CurrentWorkbook(){[Name="TableB"]}[Content],
    res = Table.AddColumn(tblA,"Distinct_Count",each List.Count(List.Distinct(Table.SelectRows(tblB,(x)=>x[STATE]=[STATE])[ATTORNEY_NAME])))
in
    res

 

[mike_viz_lord]

Thanks. Getting the following error:

Expression.Error: A cyclic reference was encountered during evaluation.

Here is the code generated with actual table and field names:

= Table.AddColumn(#"Reordered Columns1", "Custom", each let
tblA = #"State Expansion Data - Un-Pivoted",
tblB = #"Current List All Attys",
res = Table.AddColumn(tblA,"Distinct_Count",each List.Count(List.Distinct(Table.SelectRows(tblB,(x)=>x[State]=[PRACTICE STATE])[ATTY NAME])))
in
res)

[Anonymous]

You could use:

NewColumn=Table.AddColumn(LastStep, "Rows", each Table.RowCount(Table.SelectRows(TableName, each [Table_B.STATE] = [Table_A.STATE])))

[Anonymous · Accepted solution]

Sorry, that's wrong. THIS will work, and it's easier.

NewStep=Table.NestedJoin(PreviousStep, {"STATE"}, Table.Distinct(TABLE_2), {"STATE"}, "People", JoinKind.LeftOuter)

Now, instead of Expanding the table column, you can choose Aggregate, select "Count" for either column, and you are done,