Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mike_viz_lord
Frequent Visitor

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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

6 REPLIES 6
shaowu459
Resolver II
Resolver II

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

Sure, just added to original post.

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

 1.png

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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, 
 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors