Forum Discussion
How to count rows from another table in Power Query
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,
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
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)
You could use:
NewColumn=Table.AddColumn(LastStep, "Rows", each Table.RowCount(Table.SelectRows(TableName, each [Table_B.STATE] = [Table_A.STATE])))
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,