Forum Discussion

Kid_Koala's avatar
Kid_Koala
Frequent Visitor
8 years ago
Solved

Issue with merging data while using Power Bi for SEO

I'm using Power Bi for SEO, position analysis. Got two tables. First be like:

Keywords    Position on 7/08/2017
A                                10
B                                11
C                                12

The second one is:

 

Keywords        Position on 07/09/2017
A                                 20       

B                                 21

C                                 22

D                                 13

E                                  14

How can I merge them into one, which is like:

Keywords           Position on 07/09/2017             Position on 7/08/2017

A                                      10                                                  20
B                                      11                                                  21
C                                      12                                                  22
D                                      --                                                   13
E                                       --                                                   14

Sorry for the noob question.

Need Help
  • SivaMani's avatar
    SivaMani
    8 years ago

    Hi Kid_Koala,

     

    Merge Table 2 with Table 1

     

     

    Then Expand,

     

    There is no duplication. I guess this is what you are looking for. Correct me if I'm wrong

9 Replies

  • BeemsC's avatar
    BeemsC
    Resolver III
    8 years ago

    Hello,

    If you have a relationship between the 2 tables:
    Just go to the query editor -> go to the table you want to add the column too -> On the top of ur screen select Add column -> Select custom column -> Insert the data you want to add -> Apply changes

    If something is unclear let me know.
    Good luck !

    • Kid_Koala's avatar
      Kid_Koala
      Frequent Visitor
      8 years ago

      Thanks for your message, BeemsC.

      Unfortunately, that didn't help. There is a relations between those two tables, however, when I click 'custom column' I can only choose from the first table columns. When I try to do the same with the second one, I only can choose from the second table columns.

  • sumit4732's avatar
    sumit4732
    Advocate II
    8 years ago

    Hi Kid_Koala,

     

    You can use the merge option in edit queries.

    Go to Edit queries -> Combine -> Select "Keyword" column as the key from both the tables.

    And then expand the table by clicking on the icon next to new column post merge.

     

    Hope this help :) 

     

    -Sumit

    • Kid_Koala's avatar
      Kid_Koala
      Frequent Visitor
      8 years ago

      ( sumit4732 )
      Hi mate,

      The problem with you approach is, when I do it like this, I've got two columns with keywords: from table 1 and from table 2.
      It looks like:

      keywords table 1       Position table 1         keywords table 2             Position table 2
          A                                           10                                A                                      20

          B                                            11                               B                                       21

          C                                            12                               C                                       22

                                                                                           D                                       13

                                                                                           E                                        14

       

      I want to merge duplicates in keywords, but the best solution I found until now is to 'Merge tables' by keyword, so I get like:

      Keywords          position 1             position 2

      A                               10                          null

      A                             null                          20

      B                               11                          null

      B                              null                         21

      C                              12                          null

      C                              null                        22

      D                              null                        13

      E                               null                       14

      Then, I just click 'Delete Duplicates' on a column with Keywords, but it kills all the rows in position 2 with numbers, turning it into 'null'. I tryied to 'delete empty rows' in position 2 column, but it both didn't execute and seems like nonsense to me.

      • SivaMani's avatar
        SivaMani
        Resident Rockstar
        8 years ago

        Hi Kid_Koala,

         

        Merge Table 2 with Table 1

         

         

        Then Expand,

         

        There is no duplication. I guess this is what you are looking for. Correct me if I'm wrong

  • CahabaData's avatar
    CahabaData
    Memorable Member
    8 years ago

    So it probably should be pointed out that a side-by-side merge of 2 days, just one time is fine.  But if the data is coming in daily, then this approach cannot scale.

     

    Instead the data should be appended into a single table in a normal structure:

     

    Keywords     Date   Position

    A                7/8/17    10

    B                7/8/17    11

    A                7/9/17    20       
    B                7/9/17    21

    etc

     

    Then rely on Matrix visual or transform as part of the visual display and not as part of the table model.

     

    • Kid_Koala's avatar
      Kid_Koala
      Frequent Visitor
      8 years ago

      Much thanks for your answer, CahabaData

      Sure, I understand the scalability thing. Just needed that one for a one time report.