Forum Discussion
One to Many ... not so easy when using columns in a table from both tables
Assignments (1) ID Assigned To The values being Tom Richard Harry Bob Schedules (Many) ID Scheduled To Jane Beth April I've done this before (at least I think I have) an...
Hi,
Your data has not been pasted properly. Please repaste the data properly.
Assigned To
ID Name
| 1 | Tom |
| 2 | Richard |
| 3 | Harry |
| 4 | Bob |
Scheduled To
IDName
| 1 | Beth |
| 2 | Jane |
| 2 | April |
| 3 | April |
Yields This Output
NameName2CountRowsAssignedCountrowsScheduled
| Bob | April | 1 | |
| Bob | Beth | 1 | |
| Bob | Jane | 1 | |
| Harry | April | 1 | 1 |
| Harry | Beth | 1 | |
| Harry | Jane | 1 | |
| Richard | April | 1 | 1 |
| Richard | Beth | 1 | |
| Richard | Jane | 1 | 1 |
| Tom | April | 1 | |
| Tom | Beth | 1 | 1 |
| Tom | Jane | 1 |
The blanks in the countrowsscheduled column ideally should not be there because there is no matching pair.
Hi,
U used this M code
let
Source = Table.NestedJoin(Table1,{"ID Name"},Table2,{"ID Name"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Scheduled To"}, {"Scheduled To"})
in
#"Expanded Table2"Here's the result i got
I was trying to do it in DAX... not through M.
Assume you have got the third table, thus I can make changes on the table.
Create calculated columns
Column = CONCATENATE(CONCATENATE([Name]," "),[Name2]) Column 2 = CALCULATE(COUNT(Table3[CountrowsScheduled]),ALLEXCEPT(Table3,Table3[Name])) Column 3 = IF([CountrowsScheduled]=1,[Column],IF([Column 2]=BLANK(),[Name]))
Best Regards
Maggie
