Issue with merging data while using Power Bi for SEO

[Kid_Koala]

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.

[CahabaData]

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]

Much thanks for your answer, @CahabaData

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

[sumit4732]

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]

( @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 · Accepted solution]

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

[Kid_Koala]

@SivaMani thank you kind man! You've saved my life.

[SivaMani]

@Kid_Koala,

It's my pleasure

Regards,

Siva

[BeemsC]

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]

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.