Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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.
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.
@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?