none
How to fix Formula.Firewall without Ignoring Privacy Levels? Query 'x' (step 'Source') references other queries or steps and so may not directly access a data source. Please rebuild this data combination

    Question

  • Hi All,

    Is Ignoring Privacy levels the only way to fix this?   I want to share this with a number of people without asking them to Ignore Privacy.   I saw Ken Puls article from 2015 but I don't see how to do that using a function.

    Many Thanks,

    Alex

    //Query called: TableEmailAddresses

    //Get list of email addresses from Worksheet Table

    let
        Source = Excel.CurrentWorkbook(){[Name="TableEmailAddresses"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"email addresses", type text}})
    in
        #"Changed Type"

    //Function called" "get stats"

    //function that is passed an email address, grabs data from Exchange and returns a Query Table

    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

    // Pulls list of email addresses from Query TableEmailAddresses

    // For each address, pass to "get stats" which returns a pointer to Table.  Then each table is expanded and results in one table with all of the results for each of the email addresses. 

    // Results in: Formula.Firewall: Query 'Stats from Email Accounts' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

    let
        Source = TableEmailAddresses,
        #"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"

    Tuesday, October 30, 2018 10:49 AM

Answers

  • Hi Alex,

    I was wondering what happens if you do the following instead of referencing the TableEmailAddresses query:

    Source = Excel.CurrentWorkbook(){[Name="TableEmailAddresses"]}[Content]

    Ehren

    • Marked as answer by alex1alex Friday, November 2, 2018 6:12 PM
    Friday, November 2, 2018 5:06 PM
    Owner

All replies

  • Hi there. What happens if you do the addition and expansion of the "get stats" column in TableEmailAddresses directly (instead of in a separate query that references TableEmailAddresses)?

    Ehren

    Thursday, November 1, 2018 11:41 PM
    Owner
  • Hi Ehren,

    I don't get the Firewall.Formula error if I replace: 

    Source = TableEmailAddresses,

    with:

    Source = #table({"email addresses"}, {{"email1@domain.com"},{"email2@domain.com"}}),

    In the main query.  Instead, I get a popup prompting  me to set the Privacy levels for the two email addresses(which is good...it's a user actionable message :). 

    But...I'd still like to pull the email addresses from the Excel workbook so that regular users can just make changes and refresh on their own.

    Any suggestions on how to do that?

    Thanks,

    Alex


    • Edited by alex1alex Friday, November 2, 2018 2:34 PM
    Friday, November 2, 2018 2:34 PM
  • Hi Alex,

    I was wondering what happens if you do the following instead of referencing the TableEmailAddresses query:

    Source = Excel.CurrentWorkbook(){[Name="TableEmailAddresses"]}[Content]

    Ehren

    • Marked as answer by alex1alex Friday, November 2, 2018 6:12 PM
    Friday, November 2, 2018 5:06 PM
    Owner
  • Ah!   Yeah, that's it.   It now works as I'd expect.  I get the pop up asking me to set the Privacy levels for each of the email addresses.  Thanks very much, I'm happy pushing this out to users now.

    As an aside, this was pretty confusing...the Ken Puls post is from 2015.  It'd be good to make this easier to figure out.     ¯\_(ツ)_/¯

    Thanks again for you're help!

    -Alex

    Friday, November 2, 2018 6:10 PM
  • Please vote:

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35895718-fix-the-action-around-formula-firewall-so-instead

    :)

    Friday, November 2, 2018 6:24 PM