Forum Discussion

Datadork123's avatar
Datadork123
Frequent Visitor
3 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

ImkeF's avatar
ImkeF
Community Champion
2 years ago

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

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"