Hi,
Using the date and time example from exercise 7.06 all worked as per the exercise. I then decided to test myself and go further and convert the parameters into dynamic ones - this is where I have come unstuck. I've successfully managed dynamic path and folders before but can't nut this one out and suspect I've confused myself, or am over-thinking. I keep getting errors.
The want: I want to pull public holiday dates for different years, regions and holiday type combinations, where users can input into worksheet cells (using a table (or equivalent)), & simply refresh the data - the intent is for use by non-power query users.
Using dateandtime.com/holidays/Region/Year, filtered based on an chosen Holiday Type, all of which would have a data validation to ensure correct and valid selections before query refresh.
Example 1:-
Region: Australia
Year: 2024
Holiday Type: National Holidays, State Holiday (selected from the query unique list from the earlier query)
Happy for the queries to be individual given the refresh is quick.
Please Help, I'm stuck
Hi Wendy,
You have to provide your attempts and indicate the step that fails and the error message, it will be much easier to understand the problem.
thank you
Hi, apologies for the delay in coming back.
Unfortunately I had deleted the files when I couldn't get it to work. I started to recreate the attempt and got different errors again at the URL stage. I've attached the file before I'd added the lines about declaring the variable names from the worksheet range names
.
The attached screenshot shows highlighted the components I am trying to make dynamic, without creating
a parameter, unless of course I can make a parameter dynamic by using inputs from the worksheet.
Thanks, W
Hi Wendy,
Try these queries please:
Name this query "GetHolidaysData":
let
Source = Web.Page(Web.Contents("https://www.timeanddate.com/holidays/" & region & "/" & year)),
Data0 = Source{0}[Data],
#"Added Custom" = Table.AddColumn(Data0, "Custom", each [Date] & year),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Header", "Date"})
in
#"Removed Columns"
The main query is this:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}, {"Region", type text}, {"Holiday Type", type any}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each GetHolidaysData([Region],Text.From([Year]))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"", "Name", "Type", "Details", "Custom"}, {"Column1", "Name", "Type", "Details", "Custom.1"})
in
#"Expanded Custom"
</code
Hi Catalin,
I must be doing something wrong, I've copied the codes above - now I get this error on the MainQuery code. The first query seemed happy.
Formula.Firewall: Query 'MainQuery' (step 'Expanded Custom') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
Thanks,
W
Hi Wendy,
I cover Formula Firewall Errors in lesson 7.05. See if that helps. If not, please come back and let us know.
Mynda
