Append New Table if text contains in another table

[h_l]

Hello,

To make the calcuating performance better, we would like to create new smaller size table from the raw table.

Sample data PBI file for downloading.

 - 'Raw' table is the base table includes all data, will be refreshed everyday.

- 'MonitoredKeywords' table contains some texts concerned, will be updated by end user.

May I have your help to let me know how to append a new table once following criterias meet.

Expect Result:

Append a new table to include ALL records from 'Raw", if the 'MontioredKeywords'[Terms] is contained in 'Raw'[Query] when the "Domain" matches, and add 2 columns in new created table.

E.g. the new table needs to contain:

 - all records if 'Raw'[Query] contains Term 1 when 'Raw'[Domain] is Domain0

 - all records if 'Raw'[Query] contains Term 1 when 'Raw'[Domain] is Domain2

the table does NOT need to contain records if:

 - 'Raw'[Query] contains Term 1 when 'Raw'[Domain] is Domain1.

Reason: according to 'MonitoredKeyword', Term 1 is only concerned if it appears in Domain0 and Domain2.

The 2 columns need to be added in the new table,

Column1,the value of 'Montiored'[Keywords], e.g. if a Query is "Search Term 1" and Domain is domain0, then the value in this column is "Term 1".

Column2, the value: "Being Montiored".

The new table needs to be refreshed along with the 'Raw' table refresh, with data adding, change, delete.

Thank you and have a good day.

H

[v-janeyg-msft]

Hi, @h_l 

Since you need to keep all the values of the first table, why do you want to new a table without adding two columns?

Best Regards

Janey Guo

[h_l]

Hi @v-janeyg-msft ,

Let me try to explain.

In the real world, in 'Raw' table, there are over 20Million rows of data, containts seach term, URL, page view, visit, position, date, country, device, etc...the data is expanding 200K per week.

Now we have a 'keywords list' table, contains hundreds words we are concentrating.

We would like to concentrate all metrics and analysisi the performance if any word in 'keywords list' of their full performance while keeping the all metrics for all (for some comparison).

Some of the calculation cannot be finished so far, maybe because the data strcutre not good, maybe because I am writing low performance DAX, for some visual, we meet following error, sometimes we meet error about "memory not enough", etc.

So, I am considerring appened a complete new table contains everything but for those Query in 'Raw" contains any word in 'Keywords List', then maybe there will be all calculation can be done in only half of the total records in the new table.

Sorry I am not professional in database, etc., so I have no idea if create new column as you asked will help to

this situation or not.

Thanks.

H

[v-janeyg-msft]

Hi, @h_l 

If your original table has a lot of data, it will take a long time to create a new table using the lookup formula. 

Best Regards

Janey Guo