I have worked my way through PowerQuery lesson 7.01 Parameter Tables for Filtering. After viewing the video I attempted to re-create the tables and then to build the queries as shown in the tutorial but when I attempt to edit the table used to select a parameter I recieve this message. Does anyone have any idea what I am doing wrong?
Thanks,
Ron
Hi Ron,
The message screenshot didn't upload. You need to click 'start upload' after selecting your file, then wait for the grey check mark beside the file size before clicking 'submit reply'.
That said, it would be better to upload your Excel file so we can see the query in the context of the file.
Mynda
Hello again,
I seem to have deleted my original image and file for lesson 7.01.
I re-downloaded pq_7.01 and repeated the lesson and produced the same error message. This time I was more careful to save the screen shot and the file. I have uploaded both here.
Once again, can you please tell me why I receive this error when I make changes to the values in the parameter table.
Thanks so much for your help.
Hi Ronald,
The Region and Departments in the Parameter table are looking for data validation lists that aren't there. My bad, I didn't clear them out when I made the 'before' copy of the file. At 4:55 of the video you see me create the tables that support these data validation lists. If you complete those steps and reinstate the data validation lists, the query should work.
Alternatively, you can remove the data validation lists from cells G2 and H2.
Mynda
You are Awesome! Thank you so much for your help with this. One more quick thing here. When I initially pulled in the budget data table into the Power Query editor, in order to convert the period from text to a date I needed to use Using Local because I am in the USA and my machine is set to that date standard and of course this table was created based on the Austrailian standard.
It would be much easier for me to work with this data if it were converted to USA standard. After I have completed the step to convert Text to a Date using "Using Local" can I simply change it a second time to simply Date and will Power Query then change the data to a date but in USA standard?
Thank you once again for helping me with this. Chapter 7.1 was very challenging for me, but now it is clear for me. 🙂
Hi Ronald,
Glad it's working now.
When you change a date using Locale you don't need to change it a second time. It should already be in your local date format. Once you 'close & load' to Excel you can then apply cell formatting if you prefer a different date format.
Mynda
