Web.Contents dynamically passing a record

[rpiboy_1]

Hi all, I'm attempting to write a funtion that dynamically builds the entire Web.Contents query for a REST API endpoint. Right now I have it broken into two funtions. An inner function that builds the Record that is attached to the primary Web.Contents expression, and an Outer Function that builds the entire Web.Contents step. I have the basics working, now trying to add more complexity (more dynamic options)

Here is the code for the Inner function (FnWebQueryConstruct) :

(DataType as text, optional TimeOutSeconds as number, optional QueryString as record)=>
let
    #"TimeOutCheck" = TimeOutSeconds ?? 60,
    Source = [
                RelativePath="/" & DataType,
                Headers=[Accept="application/json"],
                Query = QueryString ?? [],               
                Timeout=#duration(0,0,#"TimeOutCheck",0)
            ]
        
in
    Source

Here is the outer function (FnWebQuery) :

(ServerName as text, DataType as text, optional QueryString as record)=>
let
    Source = Json.Document
        (
            Web.Contents
            (
                "https://adm-" & ServerName & "." & #"Domain URL",
                FnWebQueryConstruct(DataType, Timeout, QueryString)
            )
        )
in
    Source

Finally, I have a very simple Query (#"RawData") that looks like this:

Servers	QueryString
us	show_all="1"
ca	show_all="1"

In the #"RawData" Query I'm adding a new column by invoking my outer function that looks like this:

= Table.AddColumn(#"PreviousStep", "Projects", each FnWebQuery([Servers], "projects", [QueryString]))

The failure is occuring when it attempts to pass the 'Record' from the column [QueryString] in #"RawData". I get the classic;  'Expression.Error: "We cannot convert the value "show_all="1"" to type Record. Which I know means that my code is attempting to pass the data as text, and not as an actual record.

• How do I pass the Record from the Column?
• Any suggestions on how I should do this differently?

My eventual goal is to pass different Query Strings based on the DataType, and the Query strings would be derived from Parameters to made 'editing'/'changes' easy and eliminate the need to directly edit the M code, making the Report more accessible to a broader user set, as well as to make it more maintable and updateable if the API changes or evolves.

[ams1]

Hi @rpiboy_1 

As Query needs to be a record, you need to place something (a string) in the QueryString column that can be easily converted into a record.

One option is to place a json string inside - ex. {"show_all":"1"} - and then inside PowerQuery you can do:

=Table.AddColumn( ... Json.FromValue([QueryString]))

Not at the PC to test the above "conversion from json string to record" - maybe it's Json.Document(Text.ToBinary... 😉

Please mark this as answer if it helped.

[rpiboy_1]

@ams1 I understand what you're proposing, but I would think that there has to be a way to grab the 'record' that is there in the column...? The JSON suggestion is intriguing as it may open up some other possibilities. Will have to think about it and do some testing.

Interested to see if anyone else has any other suggestions or thoughts?