Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.