Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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.
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
Thanks for your answer Jimmy801, that was a clever workaround, but still returns the same message, I am a little bit lost.
v-juanli-msft that was my first intent, but could not make it work.
See below link to the pbix file if you want to have a look at it.
Cheers guys for your time.
Regards
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¤cy=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
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
Jimmy you are the man. Thanks for your time, finally it worked perfectly.