Forum Discussion
Power BI report connected to MS forms - Transform data with an empty excel sheet
I have a MS forms synced to an Excel sheet that is connected to Power BI desktop to show real-time data. I recently found out that when you make a connection like this you can not delete responses in the MS forms. As I put in a lot of entries, to make the building of the visuals easier to work with, the form was useless and I now created a new MS forms and Excel sheet. I have build the whole report with visuals and I therefore wonder how you work with sample data. Is it possible for me to simply upload the new excel sheet as data and just change the data? Will the visuals stay the same? My only issue is that for the new form I do not have any entries yet, so the excel sheet is all empty. I had to do a lot of changes in 'transform data' with the sample data, among other reasons because the MS Forms is in spanish so I would need to add an "if code" to change the names from spanish to english. In addition, I need to merge certain columns. What I would prefer is to now transform the data to the new excel sheet so it is all ready to just switch with the sample data, however when I tried for example the translation, it shows 'table' and when you click on it 'error'. Is there a way to fix this so I can fully prepare the data before we are starting to get MS forms entries. Thank you!
Hi Anonymous ,
I suggest that you create a parameter for the MSForms path this way you can change it to the new form when you have answers and everything should work properly if all the settings (names, columns, etc.) are the same.
https://learn.microsoft.com/en-us/power-query/power-query-query-parameters
4 Replies
- MFelix
Super User
Hi Anonymous ,
I suggest that you create a parameter for the MSForms path this way you can change it to the new form when you have answers and everything should work properly if all the settings (names, columns, etc.) are the same.
https://learn.microsoft.com/en-us/power-query/power-query-query-parameters
Thank you MFelix ! I appriciate the quick answer. The origianal two data sheets have exactly the same names/columns etc, but I have made changes to the data in the sample sheet in "transform data" so now there are a lot of new custom columns that I have added and moved around (which is what I would like to do to the new data as well). Does it still work?
- MFelix
Hi Anonymous ,
If the base columns are the same you should not have any problems with the connection to the new source, it will apply the steps in the same way and return the correct result.
Thing to be aware are the number of columns, data types (sometimes they can impact) and column names (M langauge is case sensitive) on the source data step everything else should not raise problemns becuaase are transformations on top of the data source.