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