Error Connecting To Protected Vendor web based data

RyanAlbert · August 3, 2020, 9:43pm

I am trying to connect to equipment data through an online link that a vendor provided. Here is how it looks when I bring it in.

I expand all columns out and edit but when I close and apply this is the error I get on PBi Desktop.

Error

Any advice on this would be great.

Thanks,
Ryan

Keith · August 3, 2020, 10:14pm

Hi @RyanAlbert,

Did the information come in within power query?
Did it work in the past?
The vendor might have not created the file for you to be able to pull?
Maybe your servers doesn’t allow vendor external data be imported onto the company servers. (Security risk)

The information within the file has changed and the data type that was setup before has changed and can’t convert in power bi.

It could be a lot of other things.

Maybe start your process again and see if you get the same error.

I’m just drawing at straw here.

I hope it works out for you.
Keith

samaguire · August 4, 2020, 2:18am

@RyanAlbert

Are you able to post the BPIX file? As Keith is getting at, this is hard to advise without seeing the steps you’re doing.

At face value, it seems like there is a conversion error occurring in a step where the full dataset is being pulled.

This can happen when you have poor data quality, outside of the evaluation set used when creating your power query. i.e. PQ uses the first 1,000 rows as a sample, but row 1,010 has some bad data that causes a conversion error when all the data is imported.

RyanAlbert · August 4, 2020, 1:39pm

Thanks for the quick replies, I tried running the process again and did not make any changes to column names or data types just to make sure that was not causing the problem but I am getting the same error.

I will need to get approval before I share the file solely because the vendor is a global company and I do not want to cause an issue with them, even though it is only our equipment data that we are leasing or have purchased.

I am going to reach out to our 3rd party IT company to see if they are blocking the link too.

I will post again once I have done my due diligence. Again, thanks for the responses and help with some direction for me to go.

Hemantsingh · August 4, 2020, 3:21pm

Hi @RyanAlbert Can you provide the M code…you can hide the url…i would like to see the M code from start to end (Don’t need the URL). I believe it is not related to the security rather a t some transformation step it is not able to change the step to table.

I am not sure if this is the issue but this could be one. More can be explored once you share the M Code.

Regards,
Hemant

RyanAlbert · August 4, 2020, 7:06pm

I took out the url but here is what it looks like when I first pull it in, prior to expanding the columns.

let
Source = Xml.Tables(Web.Contents("https://)),
#“Changed Type” = Table.TransformColumnTypes(Source,{{“IsLastPage”, type logical}})
in
#“Changed Type”

After expanding columns this is what it is. Once I expand all tables there are some columns that still show as “table” for the values and all columns show the red line on top for errors.

let Source =
Xml.Tables( Web.Contents( "https://" ) ),
#"Changed Type" = Table.TransformColumnTypes( Source , {{"IsLastPage" , type logical}} ) ,
#"Expanded FuelUtilization" = Table.ExpandTableColumn( #"Changed Type" , "FuelUtilization" , {"Asset" , 
     "FuelUtilizationDetails"} , {"FuelUtilization.Asset" , "FuelUtilization.FuelUtilizationDetails"} ) ,
#"Expanded FuelUtilization.Asset" = Table.ExpandTableColumn( #"Expanded FuelUtilization" , 
     "FuelUtilization.Asset" , {"VisionLinkIdentifier" , "MakeCode" , "SerialNumber" , "AssetID" , "Model" , 
      "Attribute:url"} , {"FuelUtilization.Asset.VisionLinkIdentifier" , "FuelUtilization.Asset.MakeCode" , 
      "FuelUtilization.Asset.SerialNumber" , "FuelUtilization.Asset.AssetID" , "FuelUtilization.Asset.Model" 
      , "FuelUtilization.Asset.Attribute:url"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails" = Table.ExpandTableColumn( #"Expanded 
    FuelUtilization.Asset" , "FuelUtilization.FuelUtilizationDetails" , {"FuelUtilizationDetail"} , 
    {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail" = Table.ExpandTableColumn( #"Expanded FuelUtilization.FuelUtilizationDetails" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail" , {"CalendarDayAssetLocalTime" , 
    "IdleFuelBurnedGallons" , "IdleHours" , "IdleFuelBurnRate" , "WorkingFuelBurnedGallons" , 
     "WorkingHours" , "WorkingFuelBurnRate" , "RuntimeFuelBurnedGallons" , "RuntimeHours" , 
     "RuntimeFuelBurnRate" , "WorkDefinition"} , 
     {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.CalendarDayAssetLoc" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleFuelBurnedGallo" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleHours" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleFuelBurnRate" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingFuelBurnedGa" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingHours" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingFuelBurnRate" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnedGa" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeHours" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnRate" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkDefinition"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleFuelBurnedGallo" = 
    Table.ExpandTableColumn( #"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail" , 
     "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleFuelBurnedGallo" , {"Value" , 
     "Attribute:supported" , "Attribute:received"} , 
    {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleFuelBurnedGal.1" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleFuelBurnedGal.2" , 
    "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleFuelBurnedGal.3"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleHours" = 
    Table.ExpandTableColumn( #"Expanded 
   FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleFuelBurnedGallo" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleHours" , {"Value" , "Attribute:supported" , 
   "Attribute:received"} , {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleHours.Value" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleHours.Attribute" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleHours.Attribu.1"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingFuelBurnedGa" = 
   Table.ExpandTableColumn( #"Expanded 
   FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.IdleHours" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingFuelBurnedGa" , {"Value" , 
   "Attribute:supported" , "Attribute:received"} , 
   {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingFuelBurned.1" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingFuelBurned.2" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingFuelBurned.3"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingHours" = 
   Table.ExpandTableColumn( #"Expanded 
   FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingFuelBurnedGa" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingHours" , {"Value" , 
   "Attribute:supported" , "Attribute:received"} , 
   {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingHours.Value" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingHours.Attrib" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingHours.Attr.1"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnedGa" = 
   Table.ExpandTableColumn( #"Expanded 
   FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.WorkingHours" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnedGa" , {"Value" , 
   "Attribute:supported" , "Attribute:received"} , 
   {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurned.1" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurned.2" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurned.3"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeHours" = 
   Table.ExpandTableColumn( #"Expanded 
   FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnedGa" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeHours" , {"Value" , 
   "Attribute:supported" , "Attribute:received"} , 
   {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeHours.Value" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeHours.Attrib" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeHours.Attr.1"} ) ,
#"Expanded FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnRate" = 
   Table.ExpandTableColumn( #"Expanded 
   FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeHours" , 
   "FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnRate" , {"Value"} , 
   {"FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnRa.1"} ) ,
#"Expanded Nav" = Table.ExpandTableColumn( #"Expanded 
   FuelUtilization.FuelUtilizationDetails.FuelUtilizationDetail.RuntimeFuelBurnRate" , "Nav" , {"Link"} , 
   {"Nav.Link"} ) ,
#"Expanded Nav.Link" = Table.ExpandTableColumn( #"Expanded Nav" , "Nav.Link" , {"Attribute:rel" , 
   "Attribute:href"} , {"Nav.Link.Attribute:rel" , "Nav.Link.Attribute:href"} ) 

IN 
   #"Expanded Nav.Link"

samaguire · August 5, 2020, 12:05am

@RyanAlbert

They say a picture tells a thousand words. Looking at the last screenshot I can see you have rows nearer to the end where the value is “” while the preceding rows are [table].

After your first expand, have you tried filtering out rows in the columns you are further expanded where the value is not showing as [table]?

RyanAlbert · August 5, 2020, 4:24pm

I have narrowed down where the error is occurring, there is one column that when I expand to see the values it shows the red error line on all columns, when I don’t expand it, I can save and close and i get all the data with the exception of the one column.
Here is a picture of it prior to expanding, showing no errors and code below it. It is the Efficiency Percentage column

#“Expanded AssetUtilization” = Table.ExpandTableColumn(Source, “AssetUtilization”, {“Asset”,
“AssetUtilizationDetails”}, {“AssetUtilization.Asset”, “AssetUtilization.AssetUtilizationDetails”}),
#“Expanded AssetUtilization.AssetUtilizationDetails” = Table.ExpandTableColumn(#“Expanded
AssetUtilization”, “AssetUtilization.AssetUtilizationDetails”, {“AssetUtilizationDetail”},
{“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail”}),
#“Expanded AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail” =
Table.ExpandTableColumn(#“Expanded AssetUtilization.AssetUtilizationDetails”,
“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail”, {“RunningUtilizationPercentage”,
“EfficiencyPercentage”},
{“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail.RunningUtilizati”,
“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail.EfficiencyPercen”}),
#“Renamed Columns” = Table.RenameColumns(#“Expanded
AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail”,
{{“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail.RunningUtilizati”,
“RunningUtilizatiion”},
{“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail.EfficiencyPercen”,
“EfficiencyPercentage”}}),
#“Expanded RunningUtilizatiion” = Table.ExpandTableColumn(#“Renamed Columns”,
“RunningUtilizatiion”, {“Value”}, {“RunningUtilizatiion.Value”})
in
#“Expanded RunningUtilizatiion”

#“Expanded AssetUtilization” = Table.ExpandTableColumn(Source, “AssetUtilization”, {“Asset”,
“AssetUtilizationDetails”}, {“AssetUtilization.Asset”, “AssetUtilization.AssetUtilizationDetails”}), #“Expanded AssetUtilization.AssetUtilizationDetails” = Table.ExpandTableColumn(#“Expanded
AssetUtilization”, “AssetUtilization.AssetUtilizationDetails”, {“AssetUtilizationDetail”},
{“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail”}),
#“Expanded AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail” =
Table.ExpandTableColumn(#“Expanded AssetUtilization.AssetUtilizationDetails”,
“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail”, {“RunningUtilizationPercentage”,
“EfficiencyPercentage”},
{“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail.RunningUtilizati”,
“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail.EfficiencyPercen”}),
#“Renamed Columns” = Table.RenameColumns(#“Expanded
AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail”,
{{“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail.RunningUtilizati”,
“RunningUtilizatiion”},
{“AssetUtilization.AssetUtilizationDetails.AssetUtilizationDetail.EfficiencyPercen”,
“EfficiencyPercentage”}}),
#“Expanded RunningUtilizatiion” = Table.ExpandTableColumn(#“Renamed Columns”,
“RunningUtilizatiion”, {“Value”}, {“RunningUtilizatiion.Value”}),
#“Expanded EfficiencyPercentage” = Table.ExpandTableColumn(#“Expanded RunningUtilizatiion”,
“EfficiencyPercentage”, {“Value”}, {“EfficiencyPercentage.Value”})
in
#“Expanded EfficiencyPercentage”