Forum Discussion

Datadork123's avatar
Datadork123
Frequent Visitor
2 years ago
Solved

Unable to Combine Data Error

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 be...
Need Help
  • Datadork123's avatar
    Datadork123
    2 years ago

    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's avatar
Datadork123
Frequent Visitor
2 years ago

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's avatar
Datadork123
Frequent Visitor
2 years ago

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