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.Firewal...
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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Same message Jimmy ðŸ˜«. The thing is, page number is obtained by comparing both tables to check for new items (LeftAnti). Is there any other way to replicate that list and then add a custom column calling the function? The only one I can think of is exporting that list and bring it back withing a new table but I tried to do it thought a R script and it showed me the same message again😭. I am sure there should be another workaround. Many thanks for your help.

       

      Regards

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        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