Problem getting columns into Excel Power Query


I recently updated one of my forms and changed the names of some questions so now the column name that KOBO produces changed as well.

I have an Excel sheet linked to this form and when I use the API link in Power Query, I get only one version of the column name and the older entries are left black (even though it has data)

So let’s say I have a question named (Training date) and I have submissions made already then I changed the name of that question to (Date) and I submit new data with this new modified name.

What I get is only date values for the newer submissions and blanks for the old stuff.

This happens when I add “.xlsx” to the API link like this:

If I removed “.xlsx” from the link, I get two separate columns for each version of my form and then I can merge them into a single column, however, removing “.xlsx” from the API link will also remove multi-choice columns from the query (each choice used to have a dedicated column when I used link.xlsx)

I want to have the multiple-choice answers as separate columns plus I want to see the new columns along side the old columns.

I hope my explanation was clear. and thanks for you support.

@Isslam, have you tried this article to link your data to Excel Power Query and see how it behaves?

Dear Isslam,

If you give me a little bit more explanation, I could help you to solve this issue. Try to import your data as API V2 and as an XLSX file directly.

1 Like

Hi @osmanburcu and thanks for your reply. I found the link you mentioned here:{Form ID}/export-settings/

The problem is different now. I now cannot refresh my data inside excel the way I used to do with the API V1 link. I have two Excel flies in front of me one with API 1 and the other with API 2. When Adding/deleting submissions and refreshing both files (same device, same connection), the API 1 file would reflect the changes immediately. The API 2 however, take about 5-10 minutes, I keep on refreshing and the data is loaded with no change during this time.

Yes, and I didn’t find any info about my issue.

@Isslam, is it a refresh issue with updating the submissions (when adding or removing), or is it an issue when updating a survey form (adding or removing questions)?

@Kal_Lam the issue with changing question name or adding/removing questions from the survey. The result is just one version of my survey. If I exported the submissions from KOBO and chose “all versions” that would show me everything I need. Which is what I want to show up using my Excel query, to retrieve all questions from all versions.