Unable to Combine Data Error

[Datadork123]

Hello,

I have been struggling with the unable to combine data error when running on the Power BI service.  The query runs fine on power BI desktop with no warnings or errors, however the error below is produced when trying to refresh on the service.  My guess is that the "Transaction Numbers" query (shown below) requires input from both the sharepoint file and a website that is fed through the "Scraper" function which is fed the "Tracking Number" table from the sharepoint site hence the error.  

I would appreciate any help in a solution to allow this to run on the service.  Again it runs locally with no issues so I am confused as to why it wont run when published. 

Thank you!

Query Dependencies

Error on Service.

Transaction Numbers Query

let
Source = SharePoint.Files("<Redacted Sharepoint>", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "<Redacted 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", Int64.Type}, {"Reference Number", Int64.Type}, {"Department Code", type any}, {"Tracking Number", Int64.Type}, {"MISC DELETE", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Department Code", "MISC DELETE"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Tracking Number", type text}, {"Zip", type text}, {"Reference Number", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Source.Name", "Ship Date", "Customer", "City", "State", "Zip", "Reference Number"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns1", "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"})
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"

[ImkeF]

Hi @Datadork123 ,
try hardcoding the column names for expansion, so you can omit the latest query:

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)", {"DATE", "TIME", "LOCATION", "STATUS"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"DATE", type date}, {"TIME", type time}, {"LOCATION", type text}, {"STATUS", type text}})
in
#"Changed Type1"

[Datadork123 · Accepted solution]

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

[Datadork123]

Hello,

I finally found somebody that had the exact same error.  It appears to be a bug, I am going to try and run this as a dataflow to see if it resolves the issue.

https://community.fabric.microsoft.com/t5/Power-Query/HELP-Data-source-error-Unable-to-combine-data/...

[Datadork123]

Just adding to my previous Reply: - Update

I was able to remove all helper queries yet the problem persists (only on the service). 

Current Dependencies

Newest final Query

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 Files" = Table.SelectRows(SourceX, each [Attributes]?[Hidden]? <> true),
#"AddColumn" = Table.AddColumn(#"Filtered Hidden Files", "AddColumn", each Html.Table([Details], {{"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"])),
#"Expanded AddColumn" = Table.ExpandTableColumn(AddColumn, "AddColumn", {"Column1", "Column2", "Column3", "Column4"}, {"Date", "Time", "Location", "Status"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded AddColumn", each ([Date] <> "DATE")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Time", type time}, {"Location", type text}, {"Status", type text}})
in
#"Changed Type1"

[Datadork123]

Hello,

I tried this but I think the root causes are the helper queries that are made and the data being passed to them.  I have not been able to incorporate the four helper queries that are made into the main query.  Are there any tutorials or on how to do this? Right now I am toying with the binary functions (I have a single string now of the combined concent from each of the data pulls from the Website Source) and am just lost on this issue. 

Appreciate the continued help on this.

[ImkeF]

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

[Datadork123]

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?

[Datadork123]

@ImkeF 

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"

[Datadork123]

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