locked
Get text messages sent to my phone from a automatically updating power query RRS feed

  • Question

  • I have built a power query, that refreshes every minute with new data. Is there a way that I can get this info sent to my phone every time it updates?

    If this is possible, can I only get the data sent it the info is new compared to the previous minute?

    Thanks for any help in advance!

    JP

    Monday, November 13, 2017 3:34 PM

Answers

  • Hi JP,

    There is no obvious solution to your problem. Any VBA solution would be completely outside the scope of this forum, since all the work would be happening within Excel (there is no PQ involvement at this point). A VBA solution would be challenging, since there is no simple way to detect changes in the data. Detecting new records would be okay, but deletions and updates to individual records would require maintaining a snapshot of the previous state and comparing to the new state on a record-by-record basis - not to mention the difficulty in doing all of this work during a minute update!

    Wednesday, November 15, 2017 3:46 PM

All replies

  • Alternatively, is there a way that I can get any refreshed data, to automatically copy and paste the values into a separate worksheet?
    Monday, November 13, 2017 3:50 PM
  • I'm in no way a VBA expert, but I believe that you could use VBA to reach your desired solution.

    Here's some official documentation from Microsoft about VBA and Power Query in Excel 2016

    Tuesday, November 14, 2017 2:32 AM
  • I currently have something like this with the rows updating with new data every minute (the number of rows can change based on the raw data being generated from the power query)

    I want the data to be copy and pasted into a new worksheet every minute. What kind of VBA code would I need for this? (no much VBA knowledge)

    Tuesday, November 14, 2017 2:17 PM
  • Where is this 1 minute update happening? Are you using a 1 minute background refresh of the query table in Excel? 
    Tuesday, November 14, 2017 3:24 PM
  • Are you using a 1 minute background refresh of the query table in Excel? 
    Yes correct.
    Tuesday, November 14, 2017 4:17 PM
  • Hi JP,

    There is no obvious solution to your problem. Any VBA solution would be completely outside the scope of this forum, since all the work would be happening within Excel (there is no PQ involvement at this point). A VBA solution would be challenging, since there is no simple way to detect changes in the data. Detecting new records would be okay, but deletions and updates to individual records would require maintaining a snapshot of the previous state and comparing to the new state on a record-by-record basis - not to mention the difficulty in doing all of this work during a minute update!

    Wednesday, November 15, 2017 3:46 PM
  • Hi Jake,

    My understanding is that JP needs to see only the data changes (specific rows added, specific rows deleted, or specific rows with value changes). Otherwise, there is no point in simply copying the data that can be seen by simply refreshing the data.

    Since there is already a background refresh on the table, all that needs to happen at that point is to have an event handler process the refresh and check what's changed by comparing to a previous copy of the data. 

    Even if JP wants to copy the refreshed table, you would still have to check whether a value(s) has changed, even if no row is added or deleted. But if a value changes without rows added or deleted, it would be difficult to see easily what's changed, if the whole table is copied. In fact, if rows are added or deleted, you still wouldn't necessarily know what changed.

    Wednesday, November 15, 2017 7:49 PM
  • Hi JP,

    There is no obvious solution to your problem. Any VBA solution would be completely outside the scope of this forum, since all the work would be happening within Excel (there is no PQ involvement at this point). A VBA solution would be challenging, since there is no simple way to detect changes in the data. Detecting new records would be okay, but deletions and updates to individual records would require maintaining a snapshot of the previous state and comparing to the new state on a record-by-record basis - not to mention the difficulty in doing all of this work during a minute update!

    Colin is absolutely right here. If anyone from Microsoft sees this thread they'll recommend that you use Power BI instead and use the Power BI Data Alerts :)

    This doesn't mean that you can't accomplish what you want with some serious development, is just that natively Power Query doesn't have a native feature to do exactly what you're requesting.  

    Thursday, November 16, 2017 8:35 AM
  • "If anyone from Microsoft sees this thread they'll recommend that you use Power BI instead and use the Power BI Data Alerts :)"

    Perhaps not. Power BI Desktop/Online is a completely different environment from what JP is using, and the alerts are triggered by thresholds set on dashboards created in the online service. So they don't work with raw tables, are not triggered by every change, and cannot be triggered every minute.

    Thursday, November 16, 2017 4:10 PM
  • "If anyone from Microsoft sees this thread they'll recommend that you use Power BI instead and use the Power BI Data Alerts :)"

    Perhaps not. Power BI Desktop/Online is a completely different environment from what JP is using, and the alerts are triggered by thresholds set on dashboards created in the online service. So they don't work with raw tables, are not triggered by every change, and cannot be triggered every minute.

    Don't forget that you can import your Excel queries into Power BI with just a few clicks.

    Depending on the data source, the Power BI Data Alerts could be sufficient, but if needed more Power then the Power BI API (to push data and have a Real Time Dashboard) and perhaps a combination with Flow (for email notification) would be what he needed.

    Thursday, November 16, 2017 7:43 PM
  • and you'd def love the Power BI Mobile App, Joe :)

    Check out the Power BI mobile app data alerts here.

    Thursday, November 16, 2017 8:03 PM
  • Hi Miguel,

    Power BI alerts (mobile or otherwise) are not going to give him the row level alerts that he needs. All of those tools work at the presentation layer, not at the data table layer. What Joe wants in Excel is something that you can do easily in in a product like Microsoft Access. In Access, you can use data macros to say, send an email notification after data changes (separate macros for inserts, updates, and deletes), as shown in the video at the following link: https://www.youtube.com/watch?v=ndHFdJy2EzI

    Thursday, November 16, 2017 9:45 PM
  • Hey Colin,

    Agree.  After thinking it through, I can't think of a logical way to accomplish with VBA.

    I thought you could use a loop sequence to compare the values, but the deletion of rows makes it complicated.  I am deleting my post with the code.  JP, let me know if you want the earlier sample.

    Jake

    Monday, November 20, 2017 5:17 PM