Forum Discussion

kevlarmpowered's avatar
kevlarmpowered
Icon for Helper I rankHelper I
8 years ago

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) and I can't remember how to accomplish this feat with the following result.

 

Assigned To, Scheduled To, Assigned, Scheduled

Name, Name, countrows(Assignments), countrows(Schedules)

 

When I do it, it repeats all of the ScheduledTo for each AssignedTo rows, so I get something like this (even if there are no rows in the second table).  

 

Tom Jane 

Tom Beth 

Tom April 

Richard Jane 

Richard Beth 

Richard April 

Harry Jane 

Harry Beth 

Harry April 

Bob Jane 

Bob Beth 

Bob April 

 

When I reality I want

 

Tom Beth

Richard Jane

Richard April

Harry April

Bob  blank (because Bob doesn't have related record in the ScheduledTo table)

 

I'm basically trying to count the rows of each table by the pair of people, but it's not working as I expect

Need Help

7 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi kevlarmpowered

    How do you create relationships between two tables? are they linked by ID? how does the ID match each other?

    From your information, I can't reproduce your scenario.

    Could you share me some data?

     

    Best Regards

    Maggie

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Your data has not been pasted properly.  Please repaste the data properly.

    • kevlarmpowered's avatar
      kevlarmpowered
      Icon for Helper I rankHelper I
      8 years ago

      Assigned To

      ID Name

      1Tom
      2Richard
      3Harry
      4Bob

       

      Scheduled To

      IDName

      1Beth
      2Jane
      2April
      3April

       

      Yields This Output

       

      NameName2CountRowsAssignedCountrowsScheduled

      BobApril1 
      BobBeth1 
      BobJane1 
      HarryApril11
      HarryBeth1 
      HarryJane1 
      RichardApril11
      RichardBeth1 
      RichardJane11
      TomApril1 
      TomBeth11
      TomJane1 

       

      The blanks in the countrowsscheduled column ideally should not be there because there is no matching pair.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        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