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.
Helper IApologies you can actually disregard the last message. I had the DAX missing a symbol. Your solution seems to be partially working as it did indeed pull in a closer number to a deduped value, but som of the campain_name's are missing. The logic that is identifying campaign names with a 1 or 0 isn't considering ALL campaign_name variants.
lemarcfj,
Could you please post all the data in your table and post expected result based on the sample data?
In addition, when using Google BigQuery connector in Power BI Desktop, it is not possible to write SQL statement. A method is to create a ODBC data source for bigQuery, then connect to the ODBC data source along with SQL statement.
Regards,
Lydia
yanAdvocate I
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
Thanks for your reply. I suspected that it would make mores sense to dedupe on the BQ side and you just confirmed that. I was able to get my hands on the query that I need to make this happen, but unfortunately I'm more of a hybrid strategy/technical analyst and wouldn't know what to do with the query within BQ interface as it relates to scheduling and what not.
- yan
Not for this PowerBI forum anyhow.
Research online or reach out to Stackoverflow. On the latter, do show signs that you tried something as the community is there to help, not to provide free consulting. Btw, many of us have hybrid hats - I am a tax consultant - so see this as a learning opportunity ;-)
Good luck !