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.
Community ChampionSample data and expected output would help tremendously. Does it matter if the solution is in DAX or Power Query? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
lemarcfjApologies for that. I don't care what it is as long as I get what I need. I'd imagine this would be more of a Power Query.
Sample data can be found here.
lemarcfj,
Could you please describe more details about the logic you use to get Deduped data from Raw table? I try to execute the SQL query you provide, but I found that there is no id column in the sample excel file.
Regards,Lydia
- lemarcfj
So that SQL query was just a sample query that I found from the Slack blog post here. In my sample file I believe the dimension would we'd want to use instead of "id" would be campaign name as that is the dimension that I would like to view the data by and then a deduped spend metric.