Help With query

[Arimael]

I have a Query that connects to a website and retrieves the data for the code I enter, in this case, VDM. The issue is that every time I want to obtain the data for another code, I have to open the query and edit it. Is there a way to change the code from a measure or parameter, for example?

[p45cal · Accepted solution]

One way:

create a single cell named range called say AirportCode and enter an airport code in it.

Add a step in your query (it doesn't matter where) calling it AC:

= Table.FirstValue(Excel.CurrentWorkbook(){[Name="AirportCode"]}[Content])

in the advanced query editor it would look like:

AC = Table.FirstValue(Excel.CurrentWorkbook(){[Name="AirportCode"]}[Content]),

then change your existing HTML Code step to:

= Web.BrowserContents("https://www.air-port-codes.com/airport-info/" & AC & "/")

In advanced editor it should look like:

#"HTML Code" = Web.BrowserContents("https://www.air-port-codes.com/airport-info/" & AC & "/"),

That's it.

Adjust the code in the AirportCode named range cell then refresh the query.

[Arimael]

Thank you very much, it has worked perfectly. I added a list with a for loop to automatically search for all the codes I need, and it works wonderfully. Best regards.

[Arimael]

let
#"HTML Code" = Web.BrowserContents("https://www.air-port-codes.com/airport-info/VDM/"),
#"Split Text" = Text.Split(#"HTML Code", "Airport Information"),
#"Split Text1" = #"Split Text"{1},
#"Split Text2" = Text.Split(#"Split Text1", "(Feet)"),
#"Split Text3" = #"Split Text2"{0},
#"Imported Text" = Lines.FromText(#"Split Text3"),
#"Reversed List" = List.Reverse(#"Imported Text"),
#"Reversed List1" = List.Reverse(#"Reversed List"),
#"Converted to Table" = Table.FromList(#"Reversed List1", Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if Text.Contains([Column1], ":</strong>") then "A" else "B"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "A")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","<div class=""grid_12""><strong>","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<div class=""grid_6_6""><strong>","",Replacer.ReplaceText,{"Column1"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Column1", Text.Trim, type text}}),
#"Replaced Value2" = Table.ReplaceValue(#"Trimmed Text","</div>","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","<small>","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","</strong> ","---",Replacer.ReplaceText,{"Column1"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value4",{"Custom"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column1", Splitter.SplitTextByDelimiter("---", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Column1.1] <> "Website:")),
#"Replaced Value5" = Table.ReplaceValue(#"Filtered Rows1","<a href","",Replacer.ReplaceText,{"Column1.2"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","</a>","",Replacer.ReplaceText,{"Column1.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value6",{{"Column1.1", "Titulo"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "Column1.2", Splitter.SplitTextByDelimiter(">", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
#"Replaced Value7" = Table.ReplaceValue(#"Changed Type1",null,"",Replacer.ReplaceValue,{"Column1.2.2"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value7", "Datos", each if [Column1.2.2] ="" then [Column1.2.1] else [Column1.2.2]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Column1.2.1", "Column1.2.2"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each ([Titulo] <> "Airport Keywords:" and [Titulo] <> "Elevation:" and [Titulo] <> "Full Location:" and [Titulo] <> "Province:" and [Titulo] <> "Region:" and [Titulo] <> "State:")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows2"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Airport Name:", type text}, {"Airport (IATA) Code:", type text}, {"City:", type text}, {"Country:", type text}, {"Continent:", type text}, {"Latitude:", type number}, {"Longitude:", type number}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Airport (IATA) Code:", "Airport Name:", "City:", "Country:", "Latitude:", "Longitude:", "Continent:"})
in
#"Reordered Columns"

[KNP]

Can you post the code instead of a screenshot of the code please?

It'll make it easier to help.