263

Using The RelativePath And Query Options With Web.Contents() In Power Query And...

 2 years ago
source link: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code

The Web.Contents() function in M is the key to getting data from web pages and web services, and has a number of useful – but badly documented – options that make it easier to construct urls for your web service calls.

Consider the following url:

https://data.gov.uk/api/3/action/package_search?q=cows

It is a call to the metadata api (documentation here) for https://data.gov.uk/, the UK government’s open data portal, and returns a JSON document listing all the datasets found for a search on the keyword “cows”. You can make this call using Web.Contents() quite easily like so:

However, instead of having one long string for your url (which will probably need to be constructed in a separate step) you can use the RelativePath and Query options with Web.Contents(). They are given in the second parameter of the function and passed through as fields in a record. RelativePath adds some extra text to the base url given in the first parameter for the function, while Query allows you to add query parameters to the url, and is itself a record.

So, taking the example above, if the base url for the api is https://data.gov.uk/api we can use these options like so:

Web.Contents(
[
RelativePath="3/action/package_search",
Query=[q="cows"]
]
)

RelativePath is just the string “3/action/package_search” and is added to the base url. There is just one query parameter “q”, the search query, and the search term is “cows”, so Query takes a record with one field: [q=”cows”]. If you want to specify multiple query parameters you just need to add more fields to the Query record; for example:

Web.Contents(
[
RelativePath="3/action/package_search",
Query=
[
q="cows",
rows="20"
]
]
)

Generates a call that returns 20 results, rather than the default 10:

https://data.gov.uk/api/3/action/package_search?q=cows&rows=20

Obviously these options make it easier to construct urls and the code is much clearer, but there are also other benefits to using these options which I’ll cover in another blog post soon.

Note: at the time of writing there is a bug that causes the value given in RelativePath to be appended twice when the Web.Page() function is also used. Hopefully this will be fixed soon.

UPDATE January 2021: This blog post has more details on using the Query option: 
https://blog.crossjoin.co.uk/2021/01/10/handling-multiple-url-query-parameters-with-the-same-name-using-web-contents-in-power-query-power-bi/

Like this:

Loading...

Post navigation

65 responses

  1. Pingback: Dew Drop - August 16, 2016 (#2310) - Morning Dew

  2. Pingback: daily 08/16/2016 | Cshonea's Blog

  3. Pingback: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI – Chris Webb's BI Blog

  4. Pingback: Dynamic Web.Contents() and Power BI Refresh Errors – Data Inspirations

  5. b6e2fa356cd2e6ec3b10aa17e76c86ab?s=60&d=identicon&r=gBertrand d'Arbonneau says:

    2018-05-21 Still not fixed. Thanks for your solution, it saved my day.

    Loading...

  6. Pingback: Implementing Basic Query Folding On A Web Service In Power Query/M And Power BI « Chris Webb's BI Blog

  7. Pingback: Credentials, Data Privacy Settings And Data Sources In Power Query/Power BI « Chris Webb's BI Blog

  8. picture?type=large&_md5=739a77d57929e69f150da2c3fda9ff71Wouter van den Eerenbeemt says:

    Hi Chris, quick question which keeps puzzling us. After finding this brilliant solution of yours we proceeded but ran into a new problem. The API we are calling uses the same query option 4 times.

    The URL would be something along the lines of:

    https://domain/path?&properties=name&properties=description&properties=price&hapikey=xxxxxxxxxxxxxxxxxxxxxx

    In your example this would lead to:
    Query =
    [
    properties = “value1”,
    properties = “value2”,
    properties = “value3”,
    properties = “value4”,
    hapikey = “xxxxxxxxxxxxxxxxxxxxxx”,
    ]

    I feared this would pose a problem, knowing how standard arrays work, the last value assigned to properties would over write all previous ones. And indeed Power BI M actually warns for this problem.

    Any idea how to circumvent this?

    sincerely, Wouter

    Loading...
  9. picture?type=large&_md5=f0f6baaa6d0198f46bfc26414b1d6cdaWouter van den Eerenbeemt says:

    Thank you very much for your reply. Unfortunately power bi encodes all data between “”, and although it skips the quotes, the ampersand and the equal sign all get encoded rendering the string unusable. I even installed Netmon to observe this from happening. (thankfully the api was reachable using http instead of https) I – again – am at a loss. Any new ideas?

    Loading...
  10. 969c5ab48d37aefba540605a7ddf2df1?s=60&d=identicon&r=gAlex says:

    Hi Chris,

    Thanks for this article.

    I encounter an issue where the data from the website does not return correctly it seems if I use multiple query string.

    For example,

    this works fine – the data returns correctly (not using Query option)
    Source = Json.Document(Web.Contents(“http://useragentstring.com/?uas=”&userAgent_URLEncoded&”&getJSON=all”)),

    but this will cause failure in Scheduled Data Refresh in Power BI, so I have to use the Query option, like below.

    Source = Json.Document(Web.Contents(“http://useragentstring.com/”,[Query=[uas=userAgent_URLEncoded,getJSON=”all”]])),

    However, it seems that the data is not parsed in correctly, and I’ve got unknown values for most of the records.

    Try using
    userAgent_URLEncoded = Mozilla%2F5.0%20%28Windows%20NT%2010.0%3B%20Win64%3B%20×64%29%20AppleWebKit%2F537.36%20%28KHTML%20%20like%20Gecko%29%20Chrome%2F69.0.3497.100%20Safari%2F537.36

    Do you know why?

    Thanks

    Loading...
  11. 8cf86377bd8997dfb2f487f67835cd2d?s=60&d=identicon&r=gJulian Payne says:

    We have tried using your method, and it works for PBIX and updates in the workbool, but when we upload to PBIS, and try to run the refresh there, the only APIs that will connect are those that do not use your method of breaking the relative path out from the main URL string. All of our data sources (APIs) that are called with the relative path in the main URL string work, and those where we use your method to break them out into their own relative path are unable to connect to the API for data refresh via PBIS. The same queries that work on the desktop do not connect or work in PBIS.

    Loading...
    • 5bf71b793308b6b2992dce78faa85524?s=60&d=identicon&r=gChris Webb says:

      Hmm, that suggests something else is going on here. Have you turned off data privacy checks in Power BI Desktop? If so, does refresh fail when you turn on the data privacy checks?

      Loading...
  12. 4fd8bf3727163a86a550468d8698f6ca?s=60&d=identicon&r=gThye Jansman says:
  13. e33a5c7889e0ad34643923616c7f4706?s=60&d=identicon&r=gPaul says:

    What about if the query parameter name contains a pipe?

    Like this – query_start_date|lt

    (not me, but the API i am trying to hit) – is there any way to wrap it so PowerBI accepts it? currently throws an Invalid Identifier error

    Loading...
  14. 6f0579f97d43527b787e79286d707faf?s=60&d=identicon&r=gHeath says:

    This worked perfectly for me but then I found that it requires a gateway. I’m doing straight web calls, any idea how to skip the gateway?

    Loading...
  15. a301c5f1d6b7683e92584ae0fb9919b2?s=60&d=identicon&r=gIgnacio says:

    I can’t make it work for any way in order to schedule refresh data. I have even check 404 errors. In Power Bi desktop works perfectly. It is azure devops api.

    I’m getting: “We reached the end of the buffer.. The exception was raised by the IDataReader interface. Table: Query1”

    Capacity = (ProjectSK as text, IterationSK as text, AssignedToUserSK as text) =>

    let
    Url = Web.Contents(“https://dev.azure.com”, [RelativePath=”/[Companyname]/” & ProjectSK & “/” & “_apis/work/teamsettings/iterations/” & IterationSK & “/capacities/” & AssignedToUserSK , ManualStatusHandling={404}, Query=[#”api-version”=”5.0″]] ),
    Origen = Table.FromColumns({Lines.FromBinary(Url, null, null, 65001)}),
    #”JSON analizado” = Table.TransformColumns(Origen,{},Json.Document),
    #”Se expandió Column1″ = Table.ExpandRecordColumn(#”JSON analizado”, “Column1”, {“teamMember”, “activities”, “daysOff”, “url”, “_links”}, {“Column1.teamMember”, “Column1.activities”, “Column1.daysOff”, “Column1.url”, “Column1._links”}),
    #”Se expandió Column1.activities” = Table.ExpandListColumn(#”Se expandió Column1″, “Column1.activities”),
    #”Se expandió Column1.activities1″ = Table.ExpandRecordColumn(#”Se expandió Column1.activities”, “Column1.activities”, {“capacityPerDay”, “name”}, {“Column1.activities.capacityPerDay”, “Column1.activities.name”}),
    #”Columnas quitadas” = Table.RemoveColumns(#”Se expandió Column1.activities1″,{“Column1.teamMember”, “Column1.activities.name”, “Column1.daysOff”, “Column1.url”, “Column1._links”}),
    #”Columnas con nombre cambiado” = Table.RenameColumns(#”Columnas quitadas”,{{“Column1.activities.capacityPerDay”, “capacityPerDay”}}),
    GetMetadata = Value.Metadata(Url),
    GetResponseStatus = GetMetadata[Response.Status],
    Output = if GetResponseStatus=404 then Table.FromRecords({[capacityPerDay = 0]}) else #”Columnas con nombre cambiado”
    in
    Output

    in
    Capacity

    Loading...
    • 5bf71b793308b6b2992dce78faa85524?s=60&d=identicon&r=gChris Webb says:

      It’s hard to say what’s going on here, but it doesn’t look like a problem with your code.

      Loading...
      • a301c5f1d6b7683e92584ae0fb9919b2?s=60&d=identicon&r=gIgnacio says:

        It is curious how Power Bi Service is different to Desktop. I found my solution taking [CompanyName] out of relative path to join de URL parameter in Web.Content. It doesn’t have so much sense, but it is actually working.

        Loading...
  16. bcbcef01e0f14f01c66db7f3f0c8c94a?s=60&d=identicon&r=gDaníel says:

    Since this albeit very useful post is written in 2016 it ends with “Note: at the time of writing there is a bug that causes the value given in RelativePath to be appended twice when the Web.Page() function is also used. Hopefully this will be fixed soon.”

    This bug seems to be ongoing still today.

    I can’t get my function to work with the Query parameter when I use Web.Page() around the Web.Contents(“url”, [Query=[term=value]])

    However it works when I use Web.Page(Web.Contents(“url/?term=value)) (No

    I really wanted to let my custom connection run through a gateway but stumbled upon the unsupported source error because of Web.Contents() function – hence me reading this post.

    Now I seem to have two problems instead of one. 😐

    Loading...
  17. 9c6dbb394dd088c3709929a050068e60?s=60&d=identicon&r=gamkhullar says:

    I have a API URL which works like this :
    https://jira.company.com/rest/api/2/issue/RC-2345/worklog
    to get the data from JIRA , the param is RC-2345 in this rest is static , how can we create a similar funtion to pass the JIRAID as param , i tried the below sample function but it doesnt pass it correctly.

    = (JIRAID as text) =>
    let Source = Json.Document(Web.Contents(“https://jira.company.com/rest/api/2/issue”, [Query=[JIRAID]], [RelativePath=”worklog/”]))
    in Source

    Loading...

  18. Pingback: Historical Stock Price Function in Power Query - Power BI Tips and Tricks

  19. 8525222f5383bdcb87e3017957087440?s=60&d=identicon&r=gWilliam says:

    Have you ever seen an issue where it adds a “/” into the url?
    The API I’m trying to hit is something like this:
    “www.website.com/api/measures/history?metrics=metric1,metric2,metric3&project=ProjectName
    RelativePath =”?metrics=metric1,metric2,metric3&project=”
    Query=[project=ProjectName]
    The problem is that it shoots out
    http://www.website.com/api/measures/history/?metrics=metric1,metric2,metric3&project=ProjectName
    because it adds a “/” automatically before the relativepath.

    Loading...

  20. Pingback: Tips, Gateway, Embedding and Analysis Services – Roundup #57 – 360reports.io Power BI Videos

  21. Pingback: Power BI Auditing – 360reports.io Power BI Videos

  22. eb694e1861b60c7291860d628bc530b6?s=60&d=identicon&r=gKC Kang says:

    Hi Chris,
    I have been exploring using USERNAME() or USEPRINCIPALNAME() to dynamically assign the Paranmeter value in the Web.Contents query. The idea is to extract relevant data based on Power BI login user. Using RLS is possible however this alternative (if possible) will greatly reduce the number of records extracted for respective users.
    Do you think this is possible ?

    Loading...
  23. 82b4c80918dd2a2d4538a79025625df5?s=60&d=identicon&r=gDan says:

    Chris, first of all, thanks for this incredibly helpful post! I’ve tried to follow what you have done and the code works fine in the desktop, but I’m still having problems with not being able to refresh the data when I have published to the powerbi service.

    I’m actually connecting to an ODATA service, and had to figure out how to use params with ‘$’ signs in the name, so the example below might be helpful to others doing the same thing.

    I did try and import the data using an ODATA data source, but was having problems, hence ended up going this route:

    BaseUri = “https://myurl/v1/”,
    QueryRecord =
    [ RelativePath=”tickets/”,
    Query=
    [
    token = “xxxxxxxxxx”,
    #”$select” = “id,type,origin,baseStatus,createdDate,slaSolutionDate,slaSolutionTime,resolvedIn,urgency,serviceFull,subject”,
    #”$filter” = “createdDate gt ” & FormattedDate & “-05:00 and baseStatus ne ‘Canceled'”,
    #”$top” = “1000”,
    #”$skip” = “1”
    ]
    ],
    Source = Json.Document(
    Web.Contents( BaseUri, QueryRecord )

    Anything obvious I am doing wrong?

    Thanks

    Loading...
    • 975cb3cd86e05e3112443e05a6f78ec9?s=60&d=identicon&r=gRazvan Canua says:

      Hi,
      I would also like to know the correct way to express “select” parameter from Query.

      Thanks,
      Razvan

      Loading...
    • 6d9f292fb762320cb10744df1138f552?s=60&d=identicon&r=gRyan says:

      I am also extremely interested in a solution for this problem. We are trying to use Microsoft Graph api “skiptoken” element which is preceded by a dollarsign too.

      Loading...

  24. Pingback: Chris Webb's BI Blog: Implementing Basic Query Folding On A Web Service In Power Query/M And Power BI Chris Webb's BI Blog

  25. Pingback: Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI Chris Webb's BI Blog

  26. 1e902a2bebe3c7ee7d98497614ecb2aa?s=60&d=identicon&r=gRaul Pino says:

    I have been facing this Issue for a week and tried various approaches to fix it with no positive results 🙁

    I have set a Parameter that provides the ability to make a Dynamic URL with the Following Code which would later be merged with a list-to-table Query:

    let
    Document=(Numero as number)as table =>
    let
    Source = Web.Contents(“http://www.sanaa.hn/colonias/dbo_fechas_agua_list.php?masterkey1=”&Number.ToText(Numero)&”&mastertable=dbo.barriocolonia”,[RelativePath=”Numero”]),

    #”Extracted Table From Html” = Html.Table(Source, {{“Column1”, “TABLE[id=’form_grid_8′] > TR > :nth-child(1), TABLE[id=’form_grid_8′] > * > TR > :nth-child(1)”}, {“Column2”, “TABLE[id=’form_grid_8′] > TR > :nth-child(2), TABLE[id=’form_grid_8′] > * > TR > :nth-child(2)”}, {“Column3”, “TABLE[id=’form_grid_8′] > TR > :nth-child(3), TABLE[id=’form_grid_8′] > * > TR > :nth-child(3)”}, {“Column4”, “TABLE[id=’form_grid_8′] > TR > :nth-child(4), TABLE[id=’form_grid_8′] > * > TR > :nth-child(4)”}}, [RowSelector=”TABLE[id=’form_grid_8′] > TR, TABLE[id=’form_grid_8′] > * > TR”]),
    #”Promoted Headers” = Table.PromoteHeaders(#”Extracted Table From Html”, [PromoteAllScalars=true]),
    #”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Id Colonia”, Int64.Type}, {“Barrio o Colonia”, type text}, {“Salida de Tanque”, type text}, {“Horario”, type text}})
    in

    #”Changed Type”
    in Document

    Static example of one of the URLS would be :”http://www.sanaa.hn/colonias/dbo_fechas_agua_list.php?masterkey1=1&mastertable=dbo.barriocolonia”

    How would the Relative Path and Query solution be applied in this context to solve the Refresh Solution in the PBI Service?

    Loading...
    • 1e902a2bebe3c7ee7d98497614ecb2aa?s=60&d=identicon&r=gRaul Pino says:

      Just Fixed it with help of a friend, turns out it was simpler than I thought:

      Source = Web.Contents(“http://www.sanaa.hn/colonias/dbo_fechas_agua_list.php?masterkey1=1&mastertable=dbo.barriocolonia”,[Query=[masterkey1=Number.ToText(Numero),mastertable=”dbo.barriocolonia”]]),

      Loading...
  27. 09b19d871929a4bf0877b9da4587cd61?s=60&d=identicon&r=gThai Nguyen says:

  28. Pingback: Hente data fra API’er til Power BI | Gaute Holmin - Teknologi, livet og sånt...

  29. 66b3c12a8e164d3e9bf756f41b07a482?s=60&d=identicon&r=gRaphael Dorian Schmidt says:

    I got a similiar problem with the upload refresh and i am unable to implement a RelativePath. Either I have a too big dimension or my syntax is not correct. How would you do this? The line without the Relative Path is

    WebCall = Json.Document(Web.Contents(URL & “/webapp/api/v1/salesOrder/?pageSize=1000&sort=-id&page=”&Text.From([Page])&””, [Headers=[AuthenticationToken=”” & token & “”, #”Content-Type”=”application/json”, Accept=”application/json”]]))

    Best regards
    Raphael Schmidt

    Loading...

  30. Pingback: Power BI Incremental Refresh with Web API Data Source | Data and Analytics with Dustin Ryan

  31. Pingback: Como obter dados de APIs e atualizar automaticamente - Aprenda Power BI

  32. Pingback: Resources to learn Power Query – Ninmonkey

  33. b709544ff13b4dec1a7b4cdaf0819348?s=60&d=identicon&r=gJacques Scholtz says:

    Chris Webb you beaut! Saved the day with this blog post.

    Loading...
  34. 9c7bae62e492e92f1fced1d3e1e6b76c?s=60&d=identicon&r=gJason says:

    Hi Chris,
    I am having a very similar issue with trying to connect to a SharePoint.Files() source.
    I need to be able to access a set of files that over time will move from a Development SharePoint folder to an Operations folder.
    My original plan was to connect to an excel file that would contain the url pathways (root, path, file), and then load my data using parameters against that excel file.
    It works a treat in desktop, but then I get the “Dynamic data source” error once published to a workspace.
    Is it possible to use a similar “relative path” solution using SharePoint.Files() as a source?

    Loading...

  35. Pingback: How to get and automatically refresh APIs data - Power BI Experience

  36. fc6bfbf8a6d5f0ae83a44f5c02221622?s=60&d=identicon&r=gTHEO says:

    Hi Chris,
    Dynamic URL with Web.Contents and RelativePath worked well on Power BI Report Server (May 2020).
    But since we upgraded to Power BI Report Server (October 2020) the refresh failed with this beautiful message :
    [0] -1056505856: COM error: mscorlib, An item with the same key has already been added..
    [1] -1056505856: COM error: System.Core, Sequence contains no elements.
    [2] -1055784828: The database operation was cancelled because of an earlier failure.

    Loading...
    • 5bf71b793308b6b2992dce78faa85524?s=60&d=identicon&r=gChris Webb says:

      That looks like a bug – can you open a support case

      Loading...
    • a6fcd9f94bcef587fe4cde90a7dcd326?s=60&d=identicon&r=gRyan McCauley says:

      We’re getting the same error an in our case, it looks like it’s being caused by a Table.Combine statement – it worked fine before the October release and now fails to refresh on PBI Server. Not sure if that’s your issue, but if so, just here to tell you that I’m not aware of a work-around….

      Loading...

  37. Pingback: Chris Webb's BI Blog: Handling Multiple URL Query Parameters With The Same Name Using Web.Contents In Power Query/Power BI Chris Webb's BI Blog

  38. 0c97723d1d24dc1c8144a90714626ecd?s=60&d=identicon&r=gAleksandar says:

    Need help with power Query to schedule dynamic refresh of data and keep old data

    Hello,

    I need to keep the data for the past 24 hours that I refresh in power BI desctop (thats how I model my report and dataset)
    I have premium and pro account
    I have set RangeStart and RangeEnd parameters
    schedule data refresh and update the data in first point every 15 minutes in power BI workspace ( here I get that my data source is not supported for refresh error – ” This dataset includes a dynamic data source. “)
    I have read this blog post about “Faking Out” Web.Contents (http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/), but I cannot seem to get it working.
    Below is my query, any help would be much appreciated:

    let
    EntitiesPerPage = 500,
    Limit=”&limit=” & Text.From(EntitiesPerPage),
    Url = “https://*******.********.com/api/1/rest/public/runtime/dev_org2?org_wide=true&last_hours=24” & Limit,
    GetJson = (Url) =>
    let
    RawData = Web.Contents(Url),
    Json = Json.Document(RawData)
    in Json,

    GetEntityCount = () =>
    let Url = Url & “&offset=0″,
    Json = GetJson(Url),
    Count = Json[#”response_map”],
    Count1 = Count[#”total”]
    in
    Count1,

    GetPage = (Index) =>
    let
    //(option A)offset equal to previous row count
    offset = “&offset=” & Text.From(Index * EntitiesPerPage),
    //(option B)offset equal to page numer
    //offset = “&offset=” & Text.From(Index),
    Url = Url & offset,
    Json = GetJson(Url),
    Value = Json[#”response_map”],
    Value1 = Value[#”entries”]
    in
    Value1,

    EntityCount = GetEntityCount(),
    PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount – 1 },
    Pages = List.Transform(PageIndices, each GetPage(_)),
    Entities = List.Union(Pages),
    Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Expanded Column1″ = Table.ExpandRecordColumn(Table, “Column1”, {“documents”, “state_timestamp”, “error_documents”, “label”, “path_id”, “state”, “create_time”, “duration”, “cc_label”, “runtime_label”}, {“Column1.documents”, “Column1.state_timestamp”, “Column1.error_documents”, “Column1.label”, “Column1.path_id”, “Column1.state”, “Column1.create_time”, “Column1.duration”, “Column1.cc_label”, “Column1.runtime_label”}),
    #”Renamed Columns” = Table.RenameColumns(#”Expanded Column1″,{{“Column1.cc_label”, “cc_label”}, {“Column1.create_time”, “create_time”}, {“Column1.documents”, “documents”}, {“Column1.duration”, “duration”}, {“Column1.error_documents”, “error_documents”}, {“Column1.label”, “label”}, {“Column1.path_id”, “path_id”}, {“Column1.state”, “state”}, {“Column1.runtime_label”, “runtime_label”}, {“Column1.state_timestamp”, “state_timestamp”}}),
    #”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“state_timestamp”, type datetime}, {“create_time”, type datetime}}),
    #”Filtered Rows” = Table.SelectRows(#”Changed Type”, each [state_timestamp] >= RangeStart and [state_timestamp] < RangeEnd)
    in
    #"Filtered Rows"

    Regards,

    Aleksandar

    Loading...
  39. 27bbaab39688e32329879bcdf301836d?s=60&d=identicon&r=gvidhya says:

    Hi chris,

    Iam having the same data source refresh issue. I followed your blog but still finding issue.

    Could anyone help me in creating the relative path and query for the below code

    let
    Source = Json.Document(Web.Contents(“https://nam.api.newvoicemedia.com/stats/agent-activities/interactions?end=”&Date.ToText(EndDate, “yyyy-MM-dd”)& “T23:00:00.000Z”&”&start=”&Date.ToText(StartDate, “yyyy-MM-dd”)& “T00:00:00.000Z”&””, [Headers=[Accept=”application/vnd.newvoicemedia.v3+json”, Authorization=”bearer “&GetAccessToken()]])),

    items = Source[items],
    #”Converted to Table” = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“agentId”, “start”, “status”, “duration”, “state”, “reason”, “interaction”, “channel”}, {“agentId”, “start”, “status”, “duration”, “state”, “reason”, “interaction”, “channel”}),
    #”Expanded interaction” = Table.ExpandRecordColumn(#”Expanded Column1″, “interaction”, {“guid”, “medium”, “mediumManager”, “direction”}, {“guid”, “medium”, “mediumManager”, “direction”}),
    #”Expanded channel” = Table.ExpandRecordColumn(#”Expanded interaction”, “channel”, {“guid”}, {“guid.1″}),
    #”Changed Type” = Table.TransformColumnTypes(#”Expanded channel”,{{“guid”, type text}})
    in
    #”Changed Type”

    thanks

    Loading...
  40. 9c7bae62e492e92f1fced1d3e1e6b76c?s=60&d=identicon&r=gJason says:

    Hi Chris,

    I have a small dataset of about 6 workbooks that are stored across various places in a large SharePoint site.

    I can’t use a gateway and the Folder connector, because the file paths are too many characters, and I can’t move the files.

    Using the SharePoint.Files connector works, but the report takes between 1-2hrs to refresh about 1,000 rows of data, and often fails due to api/contextinfo error.

    Using SharePoint.Contents doesn’t help in this case, as the files are spread throughout too many different folder paths.

    This got me thinking about your Web.Contents RelativePath solution. I had read it, but never attempted to implement it.

    While I can build a Dynamic Data Source that refreshes the SharePoint files in Desktop in about 1 minute using the Web.Contents approach, I can’t seem to take advantage of the RelativePath option because I can’t authenticate into the subsite.

    my code:

    Desktop Solution works a charm:

    =Excel.Workbook(Web.Contents(Root&Path&File), null, true)

    Root, Path, File are obviously Parameters, and then I use a function to call the correct details.

    So, I attempted to modify this approach for the service:

    =Excel.Workbook(Web.Contents(“MyCompany.SharePoint.com/sites/SubSite”, [RelativePath=Path&File]), null, true)

    In theory I thought this should work, because if i type “MyCompany.SharePoint.com/sites/SubSite” into my browser, I can sign in and all is good.

    BUT, in Power Query, I have to add a suffix to the URL before I can successfully authenticate:

    “MyCompany.SharePoint.com/sites/SubSite/SitePages/Home(1).aspx”

    This Suffix obviously messes with my ability to use RelativePath.

    Do you know why I can’t authenticate into “MyCompany.SharePoint.com/sites/SubSite/” by itself?

    Any ideas how I could overcome this?

    Loading...

  41. Pingback: Integrating Workspace ONE Intelligence reports with PowerBI – Arsen Bandurian: Technical Blog

  42. Pingback: Power Query - Dynamic Data Source and Web.Contents() - Hat Full of Data

  43. Pingback: powerquery - Unable to refresh dynamic data source in PowerBI service via using anonymous web API access? - androidarsenal

  44. f3765d0cf65a7aae328002f7651b8109?s=60&d=identicon&r=gSunil Kumar Tak says:

    Hi Chris,

    below is my code

    let
    BaseURL = “https://portal.k3imagine.com/gw-bi/api/v1.0/customeraccounttransactions”,
    Token = AccessToken,
    Options = [Headers=[Authorization=”Bearer ” & Token]],
    URL = BaseURL,
    //Define a function that would take step/page as parameter and return results
    Source = Json.Document(Web.Contents(URL, Options)),
    #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“transactionDate”, “customerId”, “shopId”, “posId”, “transactionReceiptId”, “updatedByClerkId”, “spend”, “balanceDifference”, “creditLimit”, “loyaltyPointsEarned”, “loyaltyPointsManuallyRewarded”, “loyaltyPointsSpent”, “type”, “currencyCode”}, {“transactionDate”, “customerId”, “shopId”, “posId”, “transactionReceiptId”, “updatedByClerkId”, “spend”, “balanceDifference”, “creditLimit”, “loyaltyPointsEarned”, “loyaltyPointsManuallyRewarded”, “loyaltyPointsSpent”, “type”, “currencyCode”}),
    #”Changed Type” = Table.TransformColumnTypes(#”Expanded Column1″,{{“transactionDate”, type datetime}, {“customerId”, Int64.Type}, {“shopId”, Int64.Type}, {“posId”, Int64.Type}, {“transactionReceiptId”, Int64.Type}, {“updatedByClerkId”, Int64.Type}, {“spend”, Int64.Type}, {“balanceDifference”, Int64.Type}, {“creditLimit”, Int64.Type}, {“loyaltyPointsEarned”, Int64.Type}, {“loyaltyPointsManuallyRewarded”, type logical}, {“loyaltyPointsSpent”, Int64.Type}, {“type”, Int64.Type}, {“currencyCode”, type text}}),
    #”Filtered Rows” = Table.SelectRows(#”Changed Type”, each ([transactionDate] #datetime(1, 1, 1, 0, 0, 0))),
    //Find the current date and time when this query runs
    CurrentDateTime = DateTimeZone.FixedUtcNow(),
    //Find yesterday’s date
    PreviousDay = Date.AddDays(DateTime.Date(CurrentDateTime),-1),
    //Put the current date and time in a new column in the table
    #”Added Custom” = Table.AddColumn(#”Filtered Rows”, “UTC Data Load Date”, each CurrentDateTime),
    #”Changed Type3″ = Table.TransformColumnTypes(#”Added Custom”,{{“UTC Data Load Date”, type datetimezone}}),
    //Add the filter required for incremental refresh
    //Only return rows in this table if:
    //a) The RangeStart parameter equals yesterday’s date, and
    //b) RangeEnd is not null (which should never be true)
    #”Filtered Rows (1)” = Table.SelectRows(#”Changed Type3″, each DateTime.Date(RangeStart)=PreviousDay and RangeEndnull)
    in
    #”Filtered Rows (1)”

    This code is working fine in Power BI Desktop but
    I am getting below error while refreshing from Power BI service.

    This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

    Please help to resolve the issue

    Loading...

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK