Power Query

[JannetteChristie]

I have the following query:

let
Source = Json.Document(Web.Contents("https://www.deeplydigital-webservices.co.uk/dutypoint/wp/wp-json/dutypoint-api/v1/booster?flow_rate=3&flow_rate_volume_unit=L&flow_rate_time_unit=S&head_pressure=2&head_pressure_unit=BAR&pump_configuration=D/S
")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"name", "weight", "price", "efficiency", "efficiency_graph", "max_head_pressure", "tolerance", "tolerance_ok", "low_tolerance_finder", "head_pressure_graph", "power_consumption", "power_consumption_graph", "curves", "consumption_score", "model", "power_supply", "dwg_link", "pdf_link", "model_link"}, {"name.1", "weight", "price", "efficiency", "efficiency_graph", "max_head_pressure", "tolerance", "tolerance_ok", "low_tolerance_finder", "head_pressure_graph", "power_consumption", "power_consumption_graph", "curves", "consumption_score", "model", "power_supply", "dwg_link", "pdf_link", "model_link"})
in
#"Expanded Value"

How do I use vba to dynamically change the source and update the table data it produces ?

 

[Ron Coderre]

You may not need VBA..
1) Create this custom Power Query function

Function Name: fnGetNamedCellVal

Query code:

(ParameterName as text) =>
let
 ParamSource = Excel.CurrentWorkbook(){[Name=ParameterName]}[Content],
 Result = ParamSource{0}[Column1]
in
Result

2) Create a named cell...DataSource
3) Store your file sourc in that cell
eg: https:/ /www. deeplydigital-webservices.co.uk/dutypoint/wp/wp-json/dutypoint-api/v1/booster?flow_rate=3&flow_rate_volume_unit=L&flow_rate_time_unit=S&head_pressure=2&head_pressure_unit=BAR&pump_configuration=D/S
NOTE: Remove the spaces in that location...I put them in to prevent MrExcel from creating a hyperlink.

4) Begin your query this way:

let
       DataLoc = fnGetNamedCellVal("DataSource"),
       Source = Json.Document(Web.Contents(DataLoc)),

If you change the DataSource contents, Power Query will use the new location in the query

Is that something you can work with?

 

[JannetteChristie]

Thanks, all working now.

I'm hoping that you can help on another issue that I have - below is the current query code.

let

Parameter = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
URL= Parameter{0}[Value],
Source = Json.Document(Web.Contents(URL)),


#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"name", "weight", "price", "efficiency", "efficiency_graph", "max_head_pressure", "tolerance", "tolerance_ok", "low_tolerance_finder", "head_pressure_graph", "power_consumption", "power_consumption_graph", "curves", "consumption_score", "model", "power_supply", "dwg_link", "pdf_link", "model_link"}, {"name.1", "weight", "price", "efficiency", "efficiency_graph", "max_head_pressure", "tolerance", "tolerance_ok", "low_tolerance_finder", "head_pressure_graph", "power_consumption", "power_consumption_graph", "curves", "consumption_score", "model", "power_supply", "dwg_link", "pdf_link", "model_link"})
in
#"Expanded Value"

How would I go about getting the column "head_pressure_graph" to actually show the data, it currently shows as List ?

 

[Ron Coderre]

You should be able to click the "Expand" button (the double-headed arrow at the top of the column) and select "Expand to new rows"
Does that help?

 

[JannetteChristie]

Perfect, thanks very much.