Forum Discussion

mike_viz_lord's avatar
mike_viz_lord
Frequent Visitor
5 years ago
Solved

How to count rows from another table in Power Query

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 Tabl...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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, 
     

     

mike_viz_lord's avatar
mike_viz_lord
Frequent Visitor
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

You could use:

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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,