Dear Team,
Whenever I try refreshing my Power BI Dashboard which the connection is via API from Kobo Server to Power BI Server I get error 502 Bad Gateway, below is username, project name, server and screenshot.
Username: npsne
Project name: Harmonized Protection Monitoring Tool Household Assessment (HHA)
Server: ‘https://eu.kobotoolbox.org/assets/aEgHKW9Xs6vH2sThaGVUuB/submissions/?format=json’
Thank you.
@baana2005, this post discussed previously should help you solve your issue:
Dear Kal,
I tried https://kc-eu.kobotoolbox.org/assets/aEgHKW9Xs6vH2sThaGVUuB/submissions/?format=json but I’m getting 404 Error
see screenshot below. Thank you.
Hi Please try this one, You need to give the correct username and password or API key.
https://eu.kobotoolbox.org/assets/aEgHKW9Xs6vH2sThaGVUuB/submissions.json
Dear @osmanburcu,
Previously I am using this URL ‘https://eu.kobotoolbox.org/assets/aEgHKW9Xs6vH2sThaGVUuB/submissions/?format=json’
before @Kal_Lam suggested a post which indicated that I should use URL https://kc-eu.kobotoolbox.org/assets/aEgHKW9Xs6vH2sThaGVUuB/submissions/?format=json
but is given me 404 error and you suggested I should use URL https://eu.kobotoolbox.org/assets/aEgHKW9Xs6vH2sThaGVUuB/submissions.json
but same error which is 502 Bad Gateway I’m getting before I posted this post, see screenshot
I’m also attaching screenshot of the Advance Editor
I will appreciate if this my issue being resolve,
Thank you.
Dear @baana2005
Hi Again, I am quite confident that the link is working, I think the problem is from your side not the link side. Can you share with me how to you pass the login credentials? Are you using username and password? using API key?
Dear @osmanburcu,
Kindly find below how I connected the Kobo server to Power BI using authentication,
Thank you/
Your method is correct, The only option is either your token code is wrong, or there is something wrong with your form ID. Those are the options i can recall for now
Dear @osmanburcu,
The Token is correct because I just checked it and also the Form ID is correct, see below the form ID
can you just copy the link to the browser and see the result? if it is working it should show the data, else should be return empty page
Dear @osmanburcu @Kal_Lam,
Kindly note that I was able to refresh the data by right click on single refresh from the panel, I observed that due to the duplication of the main form up to 47 in number which previously I used to refresh all at one and encounter the error but refreshing one by one is working, see screenshot below.
instead of clicking refresh one by one, You can disable the refresh for the table that causing the problem. While you are in power query, right click your data set and untick the include in report refresh so next time when you click the refresh button, it will refresh all data sets except this disabled one
Dear @osmanburcu,
Thank you for the suggestion but all the table I need them to be refresh because they consist useful data and that’s why I duplicated the main data to be able to do split and pivot column which if I do from the main data is adding the main information so I have no choice rather that duplicate the particular question and the analysis. I will appreciate if you have more suggestion apart from this one which will refresh all without getting error.
Thank you
Dear @tomisin ,
What I said is you need to refresh one by one to pass the error, myself I’m getting the error when I click on refresh all but when I do one by one I successfully refresh, save and publish my work in power BI,
If anyone know another alternative apart from refreshing one by one the person should inform us please. Thank you all
Thank you for the support.
However, the error still persist on my own end.
Dear @tomisin @Kal_Lam @osmanburcu
The (502) Bad Gateway error has become something else, previously when I “refresh all” then I encounter the error and go for “single refresh” per queries but now even with the “single refresh” I keep getting the error, Kobo team kindly do something about it
Hi @baana2005,
I need more information to understand the problem, because i am not sure where is the problem, it could be with the link you import the data, could be because of the authorization you provided etc. If you are willing to share the your Power BI file and username and password of the Kobo if needed. I definitely can support you with it, Else i still can try but i might missed the mistake. As some data has no issue while other one has issue, I need to see the pbix file at least
Please delete all the username, password and API key. I got what you need. I found the problem, i will let you know asap
So, the Reason of your problem is, that your data is Massive with 30000 submissions and 1000 columns and sheets. It is 50 MB in excel, it is massive as Excel file i believe. So when you would like to have it as JSON server cannot process it and you are having 502 errors due to the size of the data. Your solution is really simple. You can use the link below to import your data to Power BI, by limiting the number of submissions in each link. you need to duplicate that Power BI sheet “coping_mechanisms_in_abssence_of_service” and each duplicated data set will contain 12000 submissions separately. After the data is imported in 3 separate tables, you can append them in Power BI directly and use it as one sheet for your reporting.
You can increase of decrease the 12000 depending on your tests, 20000 might be fine as well, but my browser crashes after 12000 submissions. This should solve your problems i believe
https://eu.kobotoolbox.org/api/v2/assets/aEgHKW9Xs6vH2sThaGVUuB/data.json/?start=0&limit=12000
https://eu.kobotoolbox.org/api/v2/assets/aEgHKW9Xs6vH2sThaGVUuB/data.json/?start=12001&limit=24000
https://eu.kobotoolbox.org/api/v2/assets/aEgHKW9Xs6vH2sThaGVUuB/data.json/?start=24001
You also can check this topic, Power BI export - submissions limit , there was a similar issue with huge amount of data like you have
