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