Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Formula.Firewall message when I create a custom column.

Hello,

I am getting the following message when I try to add a Custom Column from a nested table. Can anybody refer me to some info or tell me what I am doing wrong. Many thanks.

 

'Formula.Firewall: Query 'NEW_BUY Pages (3)' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.'

 

This is the table where I get the message:

let
Source = Table.NestedJoin(DailyDataBUY, {"URL"}, CENTRAL_Sales, {"URL"}, "CENTRAL_Sales", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(Source,{"CENTRAL_Sales"}),
Page = #"Removed Columns"[Page],
#"Removed Duplicates" = List.Distinct(Page),
#"Removed Bottom Items" = List.RemoveLastN(#"Removed Duplicates",1),
#"Converted to Table" = Table.FromList(#"Removed Bottom Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Sales_GetDataPage([Column1]))
in
#"Added Custom"

I have created a list of ID's merging two previous queries, then I try to get a nested table using Sales_GetDataPage function which has a URL as Source and it is feeds by the list of ID's resulting in multiples URL's where I get my data.

 

Any help would be much appreciated.

 

Regards

 

 

 

 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    I've checked your file now more deeply. you have 2 possibilities

    Switch off the Firewall (File-> Options -> Privacy settings)

    or you combine all your code in one query like that

    let
    SourceCen = Excel.Workbook(File.Contents("yourExcelfileCentral"), null, true),
    RemovedColumnscen = Table.RemoveColumns(SourceCen,{"Name", "Item", "Kind", "Hidden"}),
    ExpandedDataCen = Table.ExpandTableColumn(RemovedColumnscen, "Data", {"Column1", "Column3"}, {"Column1", "Column3"}),
    PromotedHeadersCen = Table.PromoteHeaders(ExpandedDataCen, [PromoteAllScalars=true]),
    FilteredRowsCen = Table.SelectRows(PromotedHeadersCen, each true),
    RemovedDuplicatesCen = Table.Distinct(FilteredRowsCen),



    SourceDail = Excel.Workbook(File.Contents("yourExcelfileDaily"), null, true),
    RemovedColumnsDail = Table.RemoveColumns(SourceDail,{"Name", "Item", "Kind", "Hidden"}),
    ExpandedDataDail = Table.ExpandTableColumn(RemovedColumnsDail, "Data", {"Column1", "Column3"}, {"Column1", "Column3"}),
    PromotedHeadersDail = Table.PromoteHeaders(ExpandedDataDail, [PromoteAllScalars=true]),





    New = Table.NestedJoin(PromotedHeadersDail, {"URL"}, RemovedDuplicatesCen, {"URL"}, "CENTRAL_BelfastHousesBUY", JoinKind.LeftAnti),
    #"Removed Columns1" = Table.RemoveColumns(New,{"CENTRAL_BelfastHousesBUY"}),
    Page = #"Removed Columns1"[Page],
    #"Removed Duplicates" = List.Distinct(Page),
    #"Removed Bottom Items" = List.RemoveLastN(#"Removed Duplicates",1),
    #"Converted to Table" = Table.FromList(#"Removed Bottom Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom1" = Table.AddColumn(#"Converted to Table" , "Custom", each GetDataPage([Column1]))
in
    #"Added Custom1"

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

7 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    you can try to split this query into two queries somthing like this

    Query: Query1

    let
Source = Table.NestedJoin(DailyDataBUY, {"URL"}, CENTRAL_Sales, {"URL"}, "CENTRAL_Sales", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(Source,{"CENTRAL_Sales"}),
Page = #"Removed Columns"[Page],
#"Removed Duplicates" = List.Distinct(Page),
#"Removed Bottom Items" = List.RemoveLastN(#"Removed Duplicates",1),
#"Converted to Table" = Table.FromList(#"Removed Bottom Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
#"Converted to Table"

    Query: Query2

    let
Query1Int = Query1
#"Added Custom" = Table.AddColumn(Query1Int , "Custom", each Sales_GetDataPage([Column1]))
in
#"Added Custom"

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your answer Jimmy801, that was a clever workaround, but still returns the same message, I am a little bit lost. 

       

       

       

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello Anonymous 

         

        try to change your function like this

        (page as number) as table =>

let
    GetPage = page,
    Source = Web.BrowserContents("https://www.propertypal.com/search?sta=forSale&st=sale&currency=GBP&term=15&sort=dateHigh&excludePoa=true&pt=residential&stygrp=2&page=" & Number.ToText(GetPage)),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "A:nth-child(2) .propbox-addr"}, {"Column2", ".propbox > :nth-child(2)", each [Attributes][href]?}, {"Column3", ".price-offers"}, {"Column4", ".propbox-details:nth-child(2) > .propbox-price > SPAN.price:nth-child(1):nth-last-child(1) > .price-value"}, {"Column5", ".propbox-details:nth-child(2) > .propbox-account"}}, [RowSelector=".fav-text"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}})
in
    #"Changed Type"


        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy