Power BI Web Source Wont Update through Gateway

I have an API source I am using on a report. It works fine on my Desktop, and I added the URL and Basic Login credentials to my Gateway. I get an error that my source is unable to update, but I don't know why. When I test the connection in my Gateway it says it connects fine. For some reason my published data set doesn't recognize the report's source as the same one as the one in my gateway.

Is there any additional step required in publishing a Web Data Source in order to make it use the gateway's web source?

This is the Error I get:

This is the Gateway's connection to the API:

This is the (Much Truncated) version of the Query I am using to retrieve the data:

let 

    Url = "https://rest.avatax.com/api/v2/companies/XXXX/transactions/",

    RawData = Web.Contents(Url),

    Json    = Json.Document(RawData)

in  

    Json

asked Jan 2 at 17:47

Jon

86411133

I know it sounds dumb, have you tried making Web.Contents the first line of the query? I don't yet have personal experience with an on-prem gateway, but based on the message it sounds like it's not reading it as a valid "type" of query to refresh. Web queries are on the list of valid connections for scheduled refresh using a gateway, so maybe it needs to be the first line of the query for it read as a "Web" connection?

– Wedge
Jan 2 at 18:03

@Wedge, good thought! I did try it, but no luck. :(

– Jon
Jan 2 at 18:12

add a comment |

Is there any additional step required in publishing a Web Data Source in order to make it use the gateway's web source?

This is the Error I get:

This is the Gateway's connection to the API:

This is the (Much Truncated) version of the Query I am using to retrieve the data:

let 

    Url = "https://rest.avatax.com/api/v2/companies/XXXX/transactions/",

    RawData = Web.Contents(Url),

    Json    = Json.Document(RawData)

in  

    Json

asked Jan 2 at 17:47

Jon

86411133

I know it sounds dumb, have you tried making Web.Contents the first line of the query? I don't yet have personal experience with an on-prem gateway, but based on the message it sounds like it's not reading it as a valid "type" of query to refresh. Web queries are on the list of valid connections for scheduled refresh using a gateway, so maybe it needs to be the first line of the query for it read as a "Web" connection?

– Wedge
Jan 2 at 18:03

@Wedge, good thought! I did try it, but no luck. :(

– Jon
Jan 2 at 18:12

add a comment |

Is there any additional step required in publishing a Web Data Source in order to make it use the gateway's web source?

This is the Error I get:

This is the Gateway's connection to the API:

This is the (Much Truncated) version of the Query I am using to retrieve the data:

let 

    Url = "https://rest.avatax.com/api/v2/companies/XXXX/transactions/",

    RawData = Web.Contents(Url),

    Json    = Json.Document(RawData)

in  

    Json

asked Jan 2 at 17:47

Jon

86411133

Is there any additional step required in publishing a Web Data Source in order to make it use the gateway's web source?

This is the Error I get:

This is the Gateway's connection to the API:

This is the (Much Truncated) version of the Query I am using to retrieve the data:

let 

    Url = "https://rest.avatax.com/api/v2/companies/XXXX/transactions/",

    RawData = Web.Contents(Url),

    Json    = Json.Document(RawData)

in  

    Json

powerbi powerquery powerbi-datasource

asked Jan 2 at 17:47

Jon

86411133

asked Jan 2 at 17:47

Jon

86411133

asked Jan 2 at 17:47

Jon

86411133

asked Jan 2 at 17:47

Jon

86411133

asked Jan 2 at 17:47

Jon

86411133

I know it sounds dumb, have you tried making Web.Contents the first line of the query? I don't yet have personal experience with an on-prem gateway, but based on the message it sounds like it's not reading it as a valid "type" of query to refresh. Web queries are on the list of valid connections for scheduled refresh using a gateway, so maybe it needs to be the first line of the query for it read as a "Web" connection?

– Wedge
Jan 2 at 18:03

@Wedge, good thought! I did try it, but no luck. :(

– Jon
Jan 2 at 18:12

add a comment |

I know it sounds dumb, have you tried making Web.Contents the first line of the query? I don't yet have personal experience with an on-prem gateway, but based on the message it sounds like it's not reading it as a valid "type" of query to refresh. Web queries are on the list of valid connections for scheduled refresh using a gateway, so maybe it needs to be the first line of the query for it read as a "Web" connection?

– Wedge
Jan 2 at 18:03

@Wedge, good thought! I did try it, but no luck. :(

– Jon
Jan 2 at 18:12

I know it sounds dumb, have you tried making Web.Contents the first line of the query? I don't yet have personal experience with an on-prem gateway, but based on the message it sounds like it's not reading it as a valid "type" of query to refresh. Web queries are on the list of valid connections for scheduled refresh using a gateway, so maybe it needs to be the first line of the query for it read as a "Web" connection?

– Wedge
Jan 2 at 18:03

@Wedge, good thought! I did try it, but no luck. :(

– Jon
Jan 2 at 18:12

add a comment |

1 Answer
1

active

oldest

votes

This an incredibly annoying "feature", but the Power BI service does a different code analysis than Power BI Desktop. The way to overcome it is to present a static URL like this:

let 

    Source = Json.Document(Web.Contents("https://rest.avatax.com/api/v2/companies/XXXX/transactions/"))

in  

    Source

Chris Webb described this in more detail (along with hacks to make the URL dynamic) in this blog post:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

answered Jan 5 at 9:28

Mike Honey

11.7k11331

seems promising, I'll test it out

– Jon
Jan 7 at 14:11

1

that worked. Making the URL as a string in Web.Contents() allowed it to work. Then I was able to add dynamic values to the Query array via a parameterized function.

– Jon
Jan 7 at 15:21

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010882%2fpower-bi-web-source-wont-update-through-gateway%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

This an incredibly annoying "feature", but the Power BI service does a different code analysis than Power BI Desktop. The way to overcome it is to present a static URL like this:

let 

    Source = Json.Document(Web.Contents("https://rest.avatax.com/api/v2/companies/XXXX/transactions/"))

in  

    Source

Chris Webb described this in more detail (along with hacks to make the URL dynamic) in this blog post:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

answered Jan 5 at 9:28

Mike Honey

11.7k11331

seems promising, I'll test it out

– Jon
Jan 7 at 14:11

1

that worked. Making the URL as a string in Web.Contents() allowed it to work. Then I was able to add dynamic values to the Query array via a parameterized function.

– Jon
Jan 7 at 15:21

add a comment |

This an incredibly annoying "feature", but the Power BI service does a different code analysis than Power BI Desktop. The way to overcome it is to present a static URL like this:

let 

    Source = Json.Document(Web.Contents("https://rest.avatax.com/api/v2/companies/XXXX/transactions/"))

in  

    Source

Chris Webb described this in more detail (along with hacks to make the URL dynamic) in this blog post:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

answered Jan 5 at 9:28

Mike Honey

11.7k11331

seems promising, I'll test it out

– Jon
Jan 7 at 14:11

1

that worked. Making the URL as a string in Web.Contents() allowed it to work. Then I was able to add dynamic values to the Query array via a parameterized function.

– Jon
Jan 7 at 15:21

add a comment |

This an incredibly annoying "feature", but the Power BI service does a different code analysis than Power BI Desktop. The way to overcome it is to present a static URL like this:

let 

    Source = Json.Document(Web.Contents("https://rest.avatax.com/api/v2/companies/XXXX/transactions/"))

in  

    Source

Chris Webb described this in more detail (along with hacks to make the URL dynamic) in this blog post:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

answered Jan 5 at 9:28

Mike Honey

11.7k11331

This an incredibly annoying "feature", but the Power BI service does a different code analysis than Power BI Desktop. The way to overcome it is to present a static URL like this:

let 

    Source = Json.Document(Web.Contents("https://rest.avatax.com/api/v2/companies/XXXX/transactions/"))

in  

    Source

Chris Webb described this in more detail (along with hacks to make the URL dynamic) in this blog post:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

answered Jan 5 at 9:28

Mike Honey

11.7k11331

answered Jan 5 at 9:28

Mike Honey

11.7k11331

answered Jan 5 at 9:28

Mike Honey

11.7k11331

answered Jan 5 at 9:28

Mike Honey

11.7k11331

seems promising, I'll test it out

– Jon
Jan 7 at 14:11

1

that worked. Making the URL as a string in Web.Contents() allowed it to work. Then I was able to add dynamic values to the Query array via a parameterized function.

– Jon
Jan 7 at 15:21

add a comment |

seems promising, I'll test it out

– Jon
Jan 7 at 14:11

1

that worked. Making the URL as a string in Web.Contents() allowed it to work. Then I was able to add dynamic values to the Query array via a parameterized function.

– Jon
Jan 7 at 15:21

seems promising, I'll test it out

– Jon
Jan 7 at 14:11

that worked. Making the URL as a string in Web.Contents() allowed it to work. Then I was able to add dynamic values to the Query array via a parameterized function.

– Jon
Jan 7 at 15:21

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu