cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

 

 

 

 

1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
Community Support
Community Support

Hi @Anonymous 

Check the solution here:

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User III
Super User III

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
Not applicable

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

 

 

 

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
Not applicable

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

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

View solution in original post

Anonymous
Not applicable

Jimmy you are the man. Thanks for your time, finally it worked perfectly.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors