none
Prompt to Sign In for a Data Source is only prompted in Power Query (used to be in Excel) RRS feed

  • Question

  • I have a power query that pulls data from our exchange server.  It grabs the email accounts from a sheet, connects to each account and gets some summary data that it pulls into the Data Model.  

    It used to be (about 6 months ago) that when someone hit the refresh and wasn't logged in, they would get the normal "Microsoft Exchange" prompt where you could either pick Exchange Account or Microsoft Account. This was all with-in EXCEL.

    Now, this doesn't happens.  BUT, if they open up Power Query, and do the refresh from there, you get the prompt as normal.  Then when you go back to Excel and hit data refresh, it runs as normal.

    (the drawback here, is that it seems to only use a Preview of the data and so if there's to much data from the 1st Exchange account you never get prompted for the 2nd account....so then when you go back to Excel and hit refresh it works and then errors out then it hits the 2nd account.)

    Any idea what's going on and how to resolve?

    ( most people on are the O365 Stable Channel, I'm on the monthly channel.  Another change that happened was that we moved from Centrify to Microsoft for our Single Source Sign On Authentication.   When this happened for any of the log-in prompts to work in Excel -> File -> Option -> General, one needed to switch from the default optimize for best appearance to optimize for compatibility )

    Many Thanks,

    Alex

    Monday, August 19, 2019 3:12 PM

All replies

  • Hi Alex. If a user isn't logged in and refreshes from Excel (not from within PQ), what do they see?

    Ehren

    Tuesday, September 3, 2019 9:02 PM
    Owner
  • Hi Ehren,

    Like so:

       

    Many Thanks,

    Alex


    • Edited by alex1alex Wednesday, September 4, 2019 6:58 AM thanks
    Wednesday, September 4, 2019 6:58 AM
  • Hi Alex. I'm not able to reproduce the issue. Does it happen when a user has no Exchange credentials defined, or when their previously entered credentials expire? Also, are you using the built-in Exchange connector, or some other mechanism?

    Ehren

    Tuesday, September 24, 2019 9:14 PM
    Owner
  • Hi Ehren,

    I think both.  I opened up PowerQuery in an XLS that was giving the error and clicked Data Source Settings -> Clear Permissions and then hit refresh back in Excel and got the same.

    Only in PQ do I see this: 

    (IE, a button to enter credentials )

    Which leads to this: 

    From Excel, I see this: 

    but no way to enter the credentials.. (and this error when doing a refresh: 

    For completeness,  this is what pops up when I continue with the above Exchange Credentials windows....i blurred out my work email address...  

    Yes, I'm just using the built-in Exchange connector.  (you actually helped me out on a previous post with the code:    (link)

    // TableEmailAddresses
    let
        Source = Excel.CurrentWorkbook(){[Name="TableEmailAddresses"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"email addresses", type text}})
    in
        #"Changed Type"
    
    // Stats from Email Accounts
    let
    Source = Excel.CurrentWorkbook(){[Name="TableEmailAddresses"]}[Content],
        #"Invoked Custom Function" = Table.AddColumn(Source, "get stats", each #"get stats"([email addresses])),
        #"Expanded get stats" = Table.ExpandTableColumn(#"Invoked Custom Function", "get stats", {"DateTimeSent", "Folder Path", "Count"}, {"get stats.DateTimeSent", "get stats.Folder Path", "get stats.Count"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded get stats",{{"get stats.DateTimeSent", type date}, {"get stats.Count", Int64.Type}})
    in
        #"Changed Type"
    
    // get stats
    let
        Source = (emailaddress) => let
            Source = Exchange.Contents( emailaddress),
            Mail1 = Source{[Name="Mail"]}[Data],
        #"Extracted Date" = Table.TransformColumns(Mail1,{{"DateTimeSent", DateTime.Date}}),
            #"Removed Other Columns" = Table.SelectColumns(#"Extracted Date",{"Folder Path", "DateTimeSent", "Id"}),
        #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"DateTimeSent", "Folder Path"}, {{"Count", each Table.RowCount(_), type number}})
    in
            #"Grouped Rows"in
        Source
    
    
    
    

    Thanks,

    Alex

    Wednesday, September 25, 2019 11:23 AM
  • Thanks for the details, Alex. Would it be possible for you to share a sample file (scrubbed of any sensitive info) that illustrates the issue? That way I can see if it repros on our end or not.

    Also, it would be helpful if you could share your Excel and PQ versions.

    Ehren

    Wednesday, September 25, 2019 5:12 PM
    Owner
  • Hi Ehren,

    As requested, find on Onedrive:  LINK

    I'm on Excel for O365 MSO 16.0.11929.20234 64bit

    (I'm on the monthly channel but most of my org is on the semi annual channel so is probably on something older)

    Power Query Version (from the Diagnostics tab in Query Options)

    2.72.5556.441 64-bit

    Thanks!

    Alex

    Monday, September 30, 2019 4:02 PM
  • Hi Alex. When I open the file you shared and click Refresh All, nothing happens. It seems like none of the queries are loaded to the worksheet. Do I need to load one of them in order to reproduce the issue?

    Ehren

    Monday, September 30, 2019 6:47 PM
    Owner
  • Hi Alex. I was able to reproduce the issue (with a slightly different error message) by loading the stats query to a new sheet. Thanks for sharing the file! We'll take a look.

    Ehren

    Monday, September 30, 2019 6:55 PM
    Owner
  • Hi Ehren,

    Yes, can you set the Query "Stats from Email Accounts" to "Load to" -> "X Add this to the Data Model"

    (I turned off the the "Load to the Data Model" to get rid of any work data.....)

    You need to put an email address here in the table/cell that says "(Put email address here - not your defaul work one but another group one you have access to - the run "Stats from Email Account" and load to Data Model)"

    (If I use my default work address it just seems to work without prompting me for anything....I get the error when using other email addresses that my account has access to.)

    Thanks,

    Alex

    Monday, September 30, 2019 6:59 PM