Forum Discussion

gpiero's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
9 years ago
Solved

Create a table with a join one-to-many from Query editor table

I am looking for ad advice to solve the following problem.

 

My table listed below come from a Sharepoint Document Library. Each row identifies a category and the GUID related. In the column Utenti I have several users enabled to get the notification for the articles related to that category.

 

 

 

I have to create a single table that contains all the users. Each row of the new table should contain the ID of the related row.

For instance, after expanded all tables in a single one, all the rows that belong to ID 1 should have the same ID to garantee the join.

 

Do you think it is possble to get such result?

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    HI gpiero,

     

    If you share some detailed information about these table structures it will be help for analysis.

     

    For your scenario, I think you can use power query to get the specify table and use 'Table.Join' function to merge these tables.

     

    Formula: get specify cell value.

     

    Table1= #"Previous Steps Name"[ColumnName]{RowIndex-1}

    Sample:

     

     

    Table1= #"Changed Type"[Utenti]{0}

    Full query:

     

    let
    Source = xxxxxxxx,
    #"Changed Type" = xxxxxxx,
    Table1= #"Changed Type"[Utenti]{0},
    Table2= #"Changed Type"[Utenti]{1},
    MergeTable=Table.Join(Table1,"User",Table2,"User")
in
    MergeTable

     

    Reference link:

    Table.Join

     

    Regards,

    Xiaoxin Sheng

4 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    9 years ago

    If you expand the column "Utenti" with the arrows: Isn't that what you need?

    • gpiero's avatar
      gpiero
      Icon for Skilled Sharer rankSkilled Sharer
      9 years ago

      ImkeF

       

      Really yes. but I did not explained well my goal to get two separate table.

       

      In any case reading you answer I realized I could copy the query keeping only the column with the ID from main document library

       

       

      and after expanding the column.

       

      During my previous test I used a wrong option of Unpivot table.....

       

       

       

      Now I have got what I was looking for

       

      Thanks again for your post

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    HI gpiero,

     

    If you share some detailed information about these table structures it will be help for analysis.

     

    For your scenario, I think you can use power query to get the specify table and use 'Table.Join' function to merge these tables.

     

    Formula: get specify cell value.

     

    Table1= #"Previous Steps Name"[ColumnName]{RowIndex-1}

    Sample:

     

     

    Table1= #"Changed Type"[Utenti]{0}

    Full query:

     

    let
    Source = xxxxxxxx,
    #"Changed Type" = xxxxxxx,
    Table1= #"Changed Type"[Utenti]{0},
    Table2= #"Changed Type"[Utenti]{1},
    MergeTable=Table.Join(Table1,"User",Table2,"User")
in
    MergeTable

     

    Reference link:

    Table.Join

     

    Regards,

    Xiaoxin Sheng

    • gpiero's avatar
      gpiero
      Icon for Skilled Sharer rankSkilled Sharer
      9 years ago

      Hi Anonymous

      Thanks for you reply.  I can't share those tables due their content.

       

      Anyway, I would like to adopt you method and I am trying to apply it although it is not so clear to me.

      I'll go on and in case I'll ask you more info.

       

      Thanks again