Body
Issue/Question
How do I authorize Bedrock queries when opening a Power Query based Excel workbook for the first time?
Environment
Resolution/Answer
When opening a Excel workbook that uses Power Query to access Bedrock data for the first time, it is necessary to provide appropriate credentials and authorize all required queries contained within the workbook. Begin by clicking 'Enable Content' within the 'SECURITY WARNING' banner near the top of your Excel window (1):
If you have not previously instructed Excel to trust all workbooks opened from your current file location, you will be asked to mark the current file as a 'Trusted Document'. Click 'Yes' (2) to continue (optionally, check the box for 'Do not ask me again for network files' to decrease the frequency of this pop-up moving forward).
Next, navigate to the 'Data' ribbon of your Excel menu (3) and click the 'Refresh All' button (4) to initiate your queries.
At this point, you will receive a pop-up requesting permission to run a 'Native Database Query'. Click 'Run' (5). Depending on how the workbook is built and how many separate queries it needs to run to return all required data, you will likely need to authorize a number of queries to run. Do not be concerned by the quantity of requests. It is important you authorize all of them.
You will need to provide your credentials to run the query at this stage of the process in the 'Access SQL Server Analysis Services' pop-up. Click 'Microsoft account' (6) in the left-hand navigation menu.
Click 'Sign in' (7) and provide your Microsoft credentials, consisting of your email address and password.
With successful login, you should return to the prior pop-up noting 'You are currently signed in.' Click 'Connect' (8) to continue.
As explained above, you will likely need to continue authorizing additional runs of Native Database queries. At some point, you will be asked to set a 'Privacy Level' for the workbook. Check the box for 'Ignore Privacy Levels checks for this file' (9) and click 'Save' (10). You will likely receive this pop-up multiple times; consistently respond in the same manner.
After your initial authorization are complete, the data within the workbook will refresh. Subsequent attempts to refresh data using the same parameters should not prompt additional authorization requests, but if you change query parameters (i.e., site, scenario, year, etc.) to a combination you have not run before, you will need to click through additional 'Native Database Query' pop-ups. These authorizations are stored in the file for you and should decrease in frequency over time.