Forum Discussion
Unable to Combine Data Error
Hello Everyone - Update - Turning this query into a dataflow resolves the problem. It would be good for the Power BI devs to address this issue in the future!
Thanks
Hi Datadork123 ,
that looks like the dreaded formula firewall issue.
Please check this article how to solve:
Power Query Errors: Please Rebuild This Data Combination (excelguru.ca)
or:
Data Privacy and the Formula Firewall in Power BI / Power Query — The Power User
Also, you might run into dynamic data source issues, so make sure to check these articles as well:
Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin.co.uk)
Hello,
Thank you for the response, I have done some research on those sources and have not been able to implement them successfully.
Starting with the example here (https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/)
I have broken the query down into multiple steps
Step 1: ExcelDataPull
let
Source = SharePoint.Files("<Redacted Sharepoint Site>", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "<Redacted Excel File Name>")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Ship Date", type date}, {"Customer", type text}, {"City", type text}, {"State", type text}, {"Zip", type text}, {"Reference Number", type text}, {"Department Code", type any}, {"Tracking Number", type text}, {"MISC DELETE", type text}})
in
#"Changed Type"
Step 2: TRNumberList
let
Source = ExcelDataPull,
TrackingNumbers = Table.SelectColumns(Source, {"Tracking Number"}),
#"Removed Duplicates" = Table.Distinct(TrackingNumbers)
in
#"Removed Duplicates"
Step 3:TransactionNumberTest
let
Source = TRNumberList,
#"Invoked Custom Function" = Table.AddColumn(Source, "Details", each Scraper([Tracking Number])),
#"Expanded Details" = Table.ExpandTableColumn(#"Invoked Custom Function", "Details", {"DATE", "TIME", "LOCATION", "STATUS"}, {"Details.DATE", "Details.TIME", "Details.LOCATION", "Details.STATUS"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Details", {"Tracking Number"})
in
#"Removed Duplicates"
This runs completly fine on desktop but throws the same error on the service (updated for the new query).
It is not clear to me how this is different than the various examples out there for this problem. Any other suggestions?
I tried another data combination trying to emulate the procedure used in (https://www.youtube.com/watch?v=_MtuqO9Cj1E) which runs on the desktop but again fails on the service. Do you have any specific questions on what else I could try? All I can tell right now is that the service does not like feeding the tracking numbers into the Scraper function but the desktop has no issues.
Current Query Dependencies
Same Error
Rebuilt Step 3: TransactionNumberTest
let
Source = TRNumberList,TrackNumber = Source[Tracking Number],
#"Converted to Table" = Table.FromList(TrackNumber, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),SourceX = Table.AddColumn(#"Changed Type", "Details", each Scraper([Column1])),
#"Expanded Details" = Table.ExpandTableColumn(SourceX, "Details", {"DATE", "TIME", "LOCATION", "STATUS"}, {"Details.DATE", "Details.TIME", "Details.LOCATION", "Details.STATUS"})
in
#"Expanded Details"Scraper Function:
(TXNumber as text) as table =>
let
Source = Web.Contents("https://www.bing.com/packagetrackingv2?packNum=<Redacted Tracking Number>&carrier=FedEx",
[Query=[packNum= TXNumber ]]
),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.rpt_se > * > TR > :nth-child(1)"}, {"Column2", "TABLE.rpt_se > * > TR > :nth-child(2)"}, {"Column3", "TABLE.rpt_se > * > TR > :nth-child(3)"}, {"Column4", "TABLE.rpt_se > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.rpt_se > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DATE", type date}, {"TIME", type time}, {"LOCATION", type text}, {"STATUS", type text}})
in
#"Changed Type"I pulled in the scraper function into the TransactionNumberTest Query and it again runs on the desktop but fails on the service, whats interesting is that this time it moved the error to the sample file that is built for the query to run. I am trying to see if I can bring in what the Sample file is doing into the query and see if that helps.
New Error
TransactionNumberTest with Scraper function built in.
let
Source = TRNumberList,TrackNumber = Source[Tracking Number],
#"Converted to Table" = Table.FromList(TrackNumber, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),SourceX = Table.AddColumn(#"Changed Type", "Details", each Web.Contents("https://www.bing.com/packagetrackingv2?packNum=<Redacted>&carrier=FedEx",
[Query=[packNum= [Column1] ]]
)),#"Filtered Hidden Files1" = Table.SelectRows(SourceX, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Details])),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Invoke Custom Function1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"DATE", type date}, {"TIME", type time}, {"LOCATION", type text}, {"STATUS", type text}})
in
#"Changed Type1"Sample file (3)
let
Source = TRNumberList,TrackNumber = Source[Tracking Number],
#"Converted to Table" = Table.FromList(TrackNumber, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),SourceX = Table.AddColumn(#"Changed Type", "Details", each Web.Contents("https://www.bing.com/packagetrackingv2?packNum=<Redacted Tracking Number>&carrier=FedEx",
[Query=[packNum= [Column1] ]]
)),
Navigation1 = SourceX{0}[Details]
in
Navigation1