how to update query when the source data changes

%3CLINGO-SUB%20id%3D%22lingo-sub-2366984%22%20slang%3D%22en-US%22%3Ehow%20to%20update%20query%20when%20the%20source%20data%20changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2366984%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3Emy%20company%20is%20using%20an%20online%20data%20collection%20tool%20(%20collects%20information%20via%20mobile%20phones%20and%20tablets%20then%20download%20them%20from%20the%20laptop%20(%20from%20the%20website%20-%20the%20data%20collection%20tool%20name%20is%20%22%20FULCRUM%22))%3C%2FP%3E%3CP%3E-%20I%20imported%20those%20data%20into%20excel%20via%20power%20query%20(%20using%20the%20API%20of%20the%20website)%3C%2FP%3E%3CP%3Ethe%20problem%20is%3A%20when%20I%20needed%20to%20collect%20extra%20information%20and%20I%20added%26nbsp%3B%20to%20other%20questions%20to%20collect%20data%20%2C%20I%20can%20not%20have%20the%20new%20question%20in%20excel%26nbsp%3B%20I%20tried%20to%20update%20the%20query%20but%20it%20did%20not%20work%3C%2FP%3E%3CP%3Ecan%20you%20help%20me%20please%3C%2FP%3E%3CP%3E(please%20let%20me%20know%20if%20you%20need%20more%20clarification%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2366984%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2372412%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20update%20query%20when%20the%20source%20data%20changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2372412%22%20slang%3D%22en-US%22%3EIs%20your%20problem%20something%20like%3A%20When%20I%20setup%20the%20(power)%20query%20the%20API%20returned%20(i.e.)%205%20columns%20but%20it%20now%20returns%20(i.e.)%207%20columns%20and%20only%20the%20first%205%20are%20loaded%20to%20Excel%3F%3CBR%20%2F%3EIf%20so%2C%20post%20the%20code%20of%20your%20query%2C%20otherwise%20try%20to%20clarify%20your%20problem%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376104%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20update%20query%20when%20the%20source%20data%20changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376104%22%20slang%3D%22en-US%22%3E%3CP%3EThanks!%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20want%20check%20the%20file%20attached%20I've%20added%20extra%20explanation%20with%20screen%20shots%2C%20I've%20also%20added%20the%20code%20(%20but%20I%20am%20not%20allowed%20to%20share%20the%20API)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2377652%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20update%20query%20when%20the%20source%20data%20changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141503%22%20target%3D%22_blank%22%3E%40ahmad%20ali%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20carefully%20looked%20at%20your%20query.%20No%20suprise%2C%20everthing%20is%20hard-coded%20(field%20names)%20when%20it%20comes%20to%20record's%20expansion%2C%20hence%20your%20problem.%20In%20a%20nutshell%20you%20have%20to%20make%20everything%20dynamic%20and%20that's%20not%20something%20easy%20when%20you're%20not%20familiar%20with%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowerquery-m%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EM%3C%2FA%3E%20(PQ%20language)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20you%20deal%20with%20a%20JSON%20document%20where%20a%20record%2Flist%2Ftable%20can%20have%20nested%20records%2Flists%2Ftables%2C%20each%20of%20them%20can%20have%20nested%20records%2Flists%2Ftables...%20Expanding%20all%20is%20a%20serious%20challenge%20-%20to%20give%20you%20a%20sense%20have%20a%20look%20to%20ImkeF's%20function%20%3CA%20href%3D%22https%3A%2F%2Fwww.thebiccountant.com%2F2018%2F06%2F17%2Fautomatically-expand-all-fields-from-a-json-document-in-power-bi-and-power-query%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehere%3C%2FA%3E%20(I%20don't%20think%20her%20function%20can%20help%20you%20though)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERealistically%20speaking%20I%20don't%20see%20how%20someone%20can%20help%20without%20a%20bit%20of%20understanding%20on%20how%20the%20application%20(FULCRUM)%20works%20(re.%20records%20and%20the%20like%20it%20creates)%20and%20without%20an%20API%20key.%26nbsp%3BI'm%20afraid%20I%20can't%20help%20you%20further%20with%20this%20but%20hope%20the%20above%20explainations%20will%20help%20you%20in%20a%20way%20or%20another%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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 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