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 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

STATEINCOMEEXPENSE
IL65465413541654
NV654646534635
NC353416463546
KY654967466346

 

Table_B

STATEATTORNEY_NAME
ILJIM
ILJIM
ILBILL
ILJOHN
ILADAM
NVMIKE
NVNANCY
NVJILL
NCEMMA
NCHENRY
NCCONSUELO
NCTIM
KYBECKY
KYDAN

 

Expected Table_A

STATEINCOMEEXPENSECUSTOM_DISTINCT_ATTORNEY_COUNT
IL654654135416544
NV6546465346353
NC3534164635464
KY6549674663462
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, 
     

     

6 Replies

  • 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.

      • 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