Forum Discussion

JamesBart's avatar
JamesBart
Regular Visitor
5 years ago

Add a new column from another table without affecting other columns

Hi there!

 

I spent some time looking for an answer to this problem: I am trying to add a column from TableB to my Table A without affecting the others columns of Table A.

More specifically, my TableA contains a text column (for a wordcloud analysis) and my TableB is a StopWord list.

 

I already tried the following techniques:

  • Creating the INDEX column then adding a CUSTOM column --> it partially adds the StopWord column. Partially since the last value/row added is aligned with the rows of the other columns.
  • Created a CUSTOM column without the index (picture below) --> if I "expand to new rows", it will duplicate values from the other columns. If I "extract values", it will concatenate all value in each cell. Either way it's not what I am looking for.

 

Of course, I tried every kind of cardinality types of association between the two tables but they don't work. The "Exclude" section from the wordcloud does not take into account the value from my StopWord list.

 

Any ideas?

 

Thanks

Need Help

4 Replies

  • JamesBart's avatar
    JamesBart
    Regular Visitor
    5 years ago

    Hi amitchandak,

     

    Thanks for your answer. Here is the PBIX sample file: WeTransfer 

     

    I did try the MERGE option with a FULL OUTER join since I want to keep rows from both tables but it's not what I am looking for..

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi JamesBart ,

     

    Sorry, not very clear about what a new column you want. 

    Can you explain in detail how your new column is created and what it will be used for. Due to the data source credentials, I cannot enter Power Query to view your data.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • JamesBart's avatar
      JamesBart
      Regular Visitor
      5 years ago

      Hi Anonymous ,

       

      As explained in my first post, the initial objective was to create a word cloud with my data, using the StopWord table/column in the "Exclude" field.

      The problem I experienced was that PowerBI only accepted a "Many-To-One" (*:1) relationship between my data table and my StopWord table, which is problematic as the WordCloud did not work properly (and even so, the cardinality should be a "Many-To-Many" relationship but this did not work either, even with the cross filter).

      Since creating a relationship between the two tables does not work, I wanted to add the StopWord column to the data table, hence my question on how to do so.

       

      Basically, my StopWord list/column (initially a separate table) is 400+ rows long whereas my other columns have only 50-70 rows worth of data. So far the means used to add this StopWord column have affected the other columns: their data is spread or duplicated (see 2nd picture replying to amitchandak).

       

      Another way of saying this, I would like to find the equivalent function/mean to SELECTCOLUMN() but using multiple tables, as the function does not merge/append/change the data throughout the columns.

       

      Hope this makes sense

       

      James