"this dataset won't be refreshed" error in PowerBi service when using Web.Contents

If you get the "This dataset wont be refreshed" error in PowerBi online and the dataset refreshes fine in PowerBi Desktop, then one reason maybe in how you are using Web.Contents.

The first thing you should do is check your "Data Source Settings" in PBI-Desktop and check to see if you get the "Some data sources may not be listed because of hand-authored queries". (See: https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-sources for more info)

For a majority of users, this is most likely happening on the usage of Web.Contents.

Here is some code that caused this error for me:

uri = "https://login.microsoftonline.com/" & tenantID & "/oauth2/token",
xxx = Web.Contents(uri, [Headers = authHeaders, Content = Text.ToBinary(authQueryString)])

The problem with the above code is that PBI service does not like to refresh URLs that are computed on the fly. So to fix the above code all you have to do is, pass the dynamic parts as a RelativePath, like so:

uri = "https://login.microsoftonline.com/",
relativePath = tenantID & "/oauth2/token",
xxx = Web.Contents(uri, [RelativePath =relativePath,  Headers = authHeaders, Content = Text.ToBinary(authQueryString)])

More info: https://docs.microsoft.com/en-us/powerquery-m/web-contents

WeDo 2.0 Lego Spirograph Instructions

Instructions for building a Spirograph using a WeDo 2.0 Kit.

https://docs.google.com/presentation/d/1DZxw8S1ywhT5uZlFYH0v5XoyK4H0_8Pl_4SF1wsdLgs/edit?usp=sharing 

Based on instructions from: https://docs.google.com/presentation/d/1uxqmkhz1k8XyKeexrwzewM4RkaOjoA0kTO4Rs_mhsLc/

D365 Finance and Operations: Odata query authentication: "No P3P Policy defined" error

 My background is with D365 Sales and I have tons of code that works flawlessly using the "Service to service calls using client credentials" flow to get its authentication token. Recently, I have been working with F&O and I was trying to using the same basic code to perform an OData call against Finance & Operations. But, I was getting a "No P3P Policy defined" error. Its not a very helpful error.

After some trial and error, I was able to determine that the problem was the URL I was using for the acquisition of the token, via the "AcquireTokenAsync" method). This does not work: https://mydomain.sandbox.operations.dynamics.com/, whereas this one does: https://mydomain.sandbox.operations.dynamics.com. The only difference was the trailing slash. Once I removed it, the error went away and all my queries began working flawlessly.

Sample Code: https://gist.github.com/rajrao/455963f9bd5b9a16558b6085116b3c03

Error Details

StatusCode: 401, ReasonPhrase: 'Unauthorized', Version: 1.1, Content: System.Net.Http.HttpConnectionResponseContent, Headers:

P3P: CP="No P3P policy defined.  Read the Microsoft privacy statement at https://go.microsoft.com/fwlink/?LinkId=271135"

Other documents:

MsDocs: Service endpoints overview  

Sharing borrowed kindle library books with kids

1. Borrow the book (I use Libby, and so after borrowing, need to make sure I click on deliver to Kindle).
2. Once you have claimed the book and added it to your account,  and under account settings, click on manage "Content and Devices" (this should be the link https://www.amazon.com/hz/mycd/myx?_encoding=UTF8&ref_=ya_aw_mycd#/home/content/booksAll/dateDsc/)
3. Click on the "..." Button next to your book
4. Click on "Manage Family Library"
5. Finally add it to your kids free time library.
6. 
   

PowerBi - Attaching a Common Data Model Folder to a DataFlow - Error: path doesnt contain model.json

If you get the "The path doesn't contain model.json. Please provide the full path to the CDM folder including its definition file (its Model.json file) and try again." error, there can be many reasons for it.

Here are some things to try:

1. Make sure your url doesnt contain blob.core.windows.net.


1. When I used the desktop app StorageExplorer and copied the path to the model.json file, then the path looked like: https://xxxxxx.blob.core.windows.net/cds-crmuat1/model.json
2. The same file, the path through Azure portal's storage explorer was: https://xxxxxx.dfs.core.windows.net/cds-crmuat1/model.json.
   The 2nd path worked, but the first one didnt.
2. Make sure your permissions are correct:
   
1. In the screen shot above, what I had missed were:
1. Adding myself to the owner role on the storage account directly. Its not enough to be in here via inheritance, you need to add yourself directly.
2. Also I had to add myself to the "Storage Blob Data Owner" role.
2. The ones in blue are what the documentation asks you to do: https://docs.microsoft.com/en-us/power-bi/service-dataflows-connect-azure-data-lake-storage-gen2#grant-permissions-to-power-bi-services

Some other gotchas:

1. The storage account used for CDM needs to be the exact same storage account used by PowerBi. As of writing this blog, this is a limitation of PowerBi that I hope they will remove.
2. The storage account needs to be in the same tenant and also hosted in the same region as PowerBi.
3. If you encounter an error when trying to save a DataFlow, make sure that there is a container/filesystem named PowerBi in that storage account.

Useful resources:

Informatica's documentation is helpful: https://docs.informatica.com/integration-cloud/cloud-data-integration-connectors/h2l/prerequisites-to-use-cdm-folders-connector/prerequisites-to-use-cdm-folders-connector.html

Dynamics CRM - Alternate key fails with message "The statement has been terminated."

If you get the unhelpful message that the "Statement has been terminated.":


Look to see if you have a duplicate value in your key. Unfortunately, if you have more than 50k records, you cannot use fetchXml and either would need to load the data into a database or filter the list by date-range (but the latter option is not guaranteed to catch all the errors and so the db option is your best bet).

Here is a query to find dups based on accountNumber on account entity

 
 
   
   
   
     
   
   
 

It used to be one time that CRM used to throw an error message stating the key creation failed because of a duplicate key. In the latest version, its a rather unhelpful message.


 

git@github.com: Permission denied (publickey).

If you get the error "git@github.com: Permission denied (publickey).", try using HTTPS:
Under clone or download, "use https" instead of "use SSH".

Restarting a web-app using Logic Apps

Unfortunately its not straight forward and the help wasnt very helpful. So here are the steps and the pages I had to look up for getting the data.

1. Create a logic app and add whatever trigger you need. (I used a recurrence based trigger, but you could use a HTTP request, etc).
2.  Add a new step and then look for Azure Resource Manager. Once you click on it, search for "Invoke" and pick the "Invoke Resource Operation" action.
   
3. You will need to fill in the following parameters and here is how to find them:
   
4. Subscription: This should be available in the drop-down. Else, its your subscription Id.
5. Resource Group: The resource group in which your web-app resides
6. Resource Provider: Microsoft.Web
   This I figured out from this page: Resource providers for Azure services
   Microsoft.Web because I needed to manage an App Service.
   
7. Short Resource Id: this I figured out by going to the "Properties" section of my App Service and its the in the "ResourceId".
   
   The value will end up being something like "sites/xxxxxxx"
8. Action Name: Lets look at this param before the ClientAPIVersion.
   The function we want to invoke is "Restart" which I found via the REST API documentation pages: https://docs.microsoft.com/en-us/rest/api/appservice/webapps
   Restart: https://docs.microsoft.com/en-us/rest/api/appservice/webapps/restart
   You can check out all the other parameters that one could provide. In this case, we dont need any additional parameters.
9. Client Api Version: This is date value and you can get it from the Restart API documenation page. Its called "API Version" and can be found at the top of the page. In my case, as of writing of this post the value was "2019-08-01".
10. Final result will look like this:
    
11. Test it out by clicking run.


1. If it ran successfully, you should see a screen that looks like this
   
2. You should also check the activity log of the web-app to see if it did restart.
   

Restarting a redis cache

To restart a redis cache, here are the parameters you need for the Invoke operation:

Subscription: subscription in which the resource resides

Resource Group: resource group in which the resource resides.

Resource Provider: Microsoft.Cache

Short Resource Id: Redis/NameOfCacheInstance (can be found under properties of your cache instance)

Client API Version: 2016-04-01 (as of the writing of this post)

Action Name: forceReboot

Request Body: {"rebootType":"AllNodes"}