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, 
     

     

shaowu459's avatar
shaowu459
Resolver II
5 years ago

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's avatar
mike_viz_lord
Frequent Visitor
5 years ago

Sure, just added to original post.

  • shaowu459's avatar
    shaowu459
    Resolver II
    5 years ago

    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'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])))