Forum Discussion
Helper IDAX/Query to Dedupe Data From Append Only BigQuery Warehouse
lemarcfj you are on the wrong path. Avoid dedup'ing on the client side as you would need to import all before doing so - unless PowerQuery is smart enough to unfold correctly (i.e. translate to native SQL).
You are better off dedup'ing on BQ side and import the filtered dataset instead.
1. Don't use a BigQuery View to dedup on-the-fly as this will likely be slow.
2. Have instad a separate table that contains the unique 'campaign' records - you can schedule such query in BQ to run daily for instance
3. once table is created/refreshed, query that one in PowerBI. You would avoid ODBC driver and can use the built-in BigQuery connector as-is (and leverage DirectQuery).
However, your depup logic is unclear and need rework - for instance I thought you would be interested in summing the spend instead of the last. Even so, you will probably end up using a window fuction like ROW_NUM() to sort by whatever and keep the first record of each window (e.g. campaign).
You can search on Stackoverflow as many have asked how to dedup already - or you can ask a new question if you are specific enough.
lemarcfj,
Create the following column in your table.
RANK = RANKX(FILTER('Raw','Raw'[Facebook Campaign]=EARLIER('Raw'[Facebook Campaign])&&'Raw'[_sdc_sequence]=EARLIER('Raw'[_sdc_sequence])),'Raw'[_sdc_batched_at],,DESC)
Create the following measures in your table.
MAXSDC_SEQUENCE = CALCULATE(MAX('Raw'[_sdc_sequence]),ALLEXCEPT('Raw','Raw'[Facebook Campaign]))
chkmeasure = IF(MAX('Raw'[_sdc_sequence])=[MAXSDC_SEQUENCE]&&MAX('Raw'[RANK])=1,1,0)
Create a table visual as shown in the following screenshot, drag chkmeasure into visual level filter and set its value to 1.
Regards,
Lydia
Helper II think I need this entire query completed upon entry into the data model so that the data that I'm presenting in dashboards is already pulling from the most "recent" table. I don't have the internal resources to write the query directly into BigQuery. How can this be done so that the data pulled into the model is using this query?
Another example of where this is a problem is if a Campaign_Name doesn't have a 1 = true, it'll pull in blank values which would be the case any time I don't include the most recent batched date included in the filter. Does that make sense?