Forum Discussion
how to update query when the source data changes
Hello,
my company is using an online data collection tool ( collects information via mobile phones and tablets then download them from the laptop ( from the website - the data collection tool name is " FULCRUM"))
- I imported those data into excel via power query ( using the API of the website)
the problem is: when I needed to collect extra information and I added to other questions to collect data , I can not have the new question in excel I tried to update the query but it did not work
can you help me please
(please let me know if you need more clarification )
4 Replies
- Is your problem something like: When I setup the (power) query the API returned (i.e.) 5 columns but it now returns (i.e.) 7 columns and only the first 5 are loaded to Excel?
If so, post the code of your query, otherwise try to clarify your problemI carefully looked at your query. No suprise, everthing is hard-coded (field names) when it comes to record's expansion, hence your problem. In a nutshell you have to make everything dynamic and that's not something easy when you're not familiar with https://docs.microsoft.com/en-us/powerquery-m/ (PQ language)
Also, you deal with a JSON document where a record/list/table can have nested records/lists/tables, each of them can have nested records/lists/tables... Expanding all is a serious challenge - to give you a sense have a look to ImkeF's function https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-power-bi-and-power-query/ (I don't think her function can help you though)
Realistically speaking I don't see how someone can help without a bit of understanding on how the application (FULCRUM) works (re. records and the like it creates) and without an API key. I'm afraid I can't help you further with this but hope the above explainations will help you in a way or another
