Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
STATE | INCOME | EXPENSE |
IL | 6546541 | 3541654 |
NV | 6546465 | 34635 |
NC | 3534164 | 63546 |
KY | 65496746 | 6346 |
Table_B
STATE | ATTORNEY_NAME |
IL | JIM |
IL | JIM |
IL | BILL |
IL | JOHN |
IL | ADAM |
NV | MIKE |
NV | NANCY |
NV | JILL |
NC | EMMA |
NC | HENRY |
NC | CONSUELO |
NC | TIM |
KY | BECKY |
KY | DAN |
Expected Table_A
STATE | INCOME | EXPENSE | CUSTOM_DISTINCT_ATTORNEY_COUNT |
IL | 6546541 | 3541654 | 4 |
NV | 6546465 | 34635 | 3 |
NC | 3534164 | 63546 | 4 |
KY | 65496746 | 6346 | 2 |
Solved! Go to 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,
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
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,