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
Dear @osmanburcu
if I understand your message is for me to limit import in to 3 or 4 batches then append them after importing,
what I did previously id import the data directly using one file name “Data” and replicate it into 51 queries do to some of the question is multiple selection which I cannot split and unpivot them because is increasing the main data, below is the screenshot of the main data which i cycle it and replicated data, also I have cycle the number of queries
Yes this is what i exactly said, You don’t need to do this duplication to all forms, just do it to the one you can’t refresh
Dear @osmanburcu
But you know I duplicate all of them and when refreshing I’m getting error in all of them, I have to do new designed by using the limitation you suggested
I suggest you using the API V2 if you would like to do a new design, what i could suggest you is, you can create a new data set, where you use the limitation and merge them as one data, later you can use this table as a reference to other tables and the rest should be done by power bi automatically
Dear @osmanburcu @Kal_Lam
I have successfully downloaded the data in different batches like is show in this message I reply
but is skips some of my field e.g. “section_2_general_protection/no_family_members_loc” is completely missing and if I check the main data in kobo I can see the field, kindly help. Thanks
Dear @baana2005,
As i mention in the previous message, i can’t comment anymore because i told everything possible. If you are willing to share the Power BI pbix file and kobo user name and password, please drop it to me a private message and let me review and come back to you.
As you can see from the previous messages, i couldn’t predict how many columns, how many submissions, how big data you have, the structure of the data. It is much easier for me to spot the mistake if i see the data.
Hi
@baana2005, I checked the Power BI and i can find the column you mentioned in the dataset, would you be missing it by mistake?
Dear @osmanburcu
The first file in the folder was before I used the new data which you help me separate it in to three batches, I have upload the new file with name" Protection Monitoring Interactive Dashboard v2 (1).pbix" the new data which remove the field mention above
Delete the last expended column1 step, because while you are importing the data you did not click the load more button probably,
After you delete the step, click the double arrow, and click the load more sentence, after it loads all the columns just click the OK to proceed. Your column should appear in the data set.
if you are creating your dashboard again, I highly suggest you to use API v2 and you can directly import it as Excel file, so i believe you will not need to import your data as three separated file and will not need to replace your values, it will decrease your work load as well as the duration of the updates. Please check the video below, it might give you some idea and if you need help, let me know
