how to update query when the source data changes



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 problem

Thanks! @L z. 

You may want check the file attached I've added extra explanation with screen shots, I've also added the code ( but I am not allowed to share the API)


Many thanks!

@ahmad ali 

I 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 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 here (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

thanks, L Z.

looks like the solution is to generate a new API token then replace it with the one in the Query advanced editor