OLE DB or ODBC error: Exception from HRESULT

[Anonymous]

Hi,

Total rookie here, looking for some guidance on how to solve the below issues.

I am scraping stock information from https://www.systembolaget.se/ to get a net sale (morning stock - evening stock).

However, I'm not sure if they did an update on their website or if something else happened (for instance I extracted a CSV-file and suddenly this issue came? not sure if that's related).

Now the following error message comes:

I have tried to identify the issue, and the error seems to derive from the GetProductData-function:

Where the following error message is shown:

Any ideas on how to solve this? Most other forum threads refer to data type and encodings, but I feel like that's not the answer here, but I could be wrong.

Plz help

Best regards, thanks!

[Anonymous]

Hi @Anonymous ,

According to the error message on my side, it seems that there is another table 

Could you please share me with your pbix file after removing sensitive data?

Best Regards,
Eyelyn Qin

[Anonymous]

Hi,

Thank you for your reponse. I'm not sure exactly how to define the issue, so here is the code from advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Appended Query" = Table.Combine({Source, #"Enter URLs to Analyse"}),
    #"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"Data"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "GetProductData", each GetProductData([Systembolaget Produkt URL])),
    #"Expanded GetProductData" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetProductData", {"information", "productId", "productNumberShort", "assortmentText", "drub", "isNewVintage", "originLevel2", "originLevel1", "originLevel3", "originLevel4", "originLevel5", "brandOrigin", "bottleCode", "bottleTypeGroup", "seal", "vatCode", "priceExclVat", "priorPrice", "comparisonPrice", "originalSellStartDate", "productLaunchDate", "productLaunchTime", "producerName", "production", "cultivationArea", "harvest", "soil", "supplierName", "isSupplierTemporaryNotAvailable", "isSupplierNotAvailable", "backInStockAtSupplier", "isDiscontinued", "isTemporaryOutOfStock", "isCompletelyOutOfStock", "isOutOfStockAtDepot", "restrictedParcelQuantity", "isDefaultProduct", "isRegionalRestricted", "isLimitedEdition", "isHidden", "isSearchable", "isInAnyStoreSearchAssortment", "isStoreOrderApplicable", "isHomeOrderApplicable", "isAgentOrderApplicable", "seasonName", "alcoholPercentage", "tastingDate", "tasteSymbols", "additives", "storage", "preservable", "hasInbounddeliveries", "isManufacturingCountry", "isGlutenFree", "isEthical", "ethicalLabel", "isKosher", "isNaturalWine", "isVeganFriendly", "isInSelectedAssortment", "isEcoFriendlyPackage", "isDepotDelivered", "availableNumberOfStores", "packaging", "gtinPrimary", "gtinSecondary", "images", "productNumber", "productNameBold", "productNameThin", "priceInclVat", "priceInclVatExclRecycleFee", "isOrganic", "isLightWeightBottle", "volume", "vintage", "country", "categoryLevel1", "categoryLevel2", "categoryLevel3", "categoryLevel4", "customCategoryTitle", "recycleFee", "recycleFeeIndicator", "bottleTextShort", "isWebLaunch", "isAddableToBasket", "productNutritionHeaders", "allergens", "ingredients", "isDKI", "showAdditionalBsInformation", "color", "aroma", "additionalInformation", "isProductLaunchDateHighlighted", "producerDescription", "grapes", "rawMaterial", "sugarContent", "tasteSymbolsList", "isTasteAndUsageAlone", "hasAnyTaste", "hasRestrictions", "hasSymbols", "hasAnyTasteSymbols", "webLaunchStart", "webLaunchEnd", "priceExclVatExclRecycleFee", "isDraughtRecycleFeeProduct", "isNews", "isNewInAssortment", "assortment", "tasteClockGroup", "tasteClocks", "taste", "usage", "tasteAndUsage", "isBsAssortment", "isFsTsAssortment", "isFsAssortment", "isTsAssortment", "isTseAssortment", "isTsLsAssortment", "isTssAssortment", "isTstAssortment", "isTsvAssortment", "isPaAssortment", "hasAnyTasteClocks"}, {"information", "productId", "productNumberShort", "assortmentText", "drub", "isNewVintage", "originLevel2", "originLevel1", "originLevel3", "originLevel4", "originLevel5", "brandOrigin", "bottleCode", "bottleTypeGroup", "seal", "vatCode", "priceExclVat", "priorPrice", "comparisonPrice", "originalSellStartDate", "productLaunchDate", "productLaunchTime", "producerName", "production", "cultivationArea", "harvest", "soil", "supplierName", "isSupplierTemporaryNotAvailable", "isSupplierNotAvailable", "backInStockAtSupplier", "isDiscontinued", "isTemporaryOutOfStock", "isCompletelyOutOfStock", "isOutOfStockAtDepot", "restrictedParcelQuantity", "isDefaultProduct", "isRegionalRestricted", "isLimitedEdition", "isHidden", "isSearchable", "isInAnyStoreSearchAssortment", "isStoreOrderApplicable", "isHomeOrderApplicable", "isAgentOrderApplicable", "seasonName", "alcoholPercentage", "tastingDate", "tasteSymbols", "additives", "storage", "preservable", "hasInbounddeliveries", "isManufacturingCountry", "isGlutenFree", "isEthical", "ethicalLabel", "isKosher", "isNaturalWine", "isVeganFriendly", "isInSelectedAssortment", "isEcoFriendlyPackage", "isDepotDelivered", "availableNumberOfStores", "packaging", "gtinPrimary", "gtinSecondary", "images", "productNumber", "productNameBold", "productNameThin", "priceInclVat", "priceInclVatExclRecycleFee", "isOrganic", "isLightWeightBottle", "volume", "vintage", "country", "categoryLevel1", "categoryLevel2", "categoryLevel3", "categoryLevel4", "customCategoryTitle", "recycleFee", "recycleFeeIndicator", "bottleTextShort", "isWebLaunch", "isAddableToBasket", "productNutritionHeaders", "allergens", "ingredients", "isDKI", "showAdditionalBsInformation", "color", "aroma", "additionalInformation", "isProductLaunchDateHighlighted", "producerDescription", "grapes", "rawMaterial", "sugarContent", "tasteSymbolsList", "isTasteAndUsageAlone", "hasAnyTaste", "hasRestrictions", "hasSymbols", "hasAnyTasteSymbols", "webLaunchStart", "webLaunchEnd", "priceExclVatExclRecycleFee", "isDraughtRecycleFeeProduct", "isNews", "isNewInAssortment", "assortment", "tasteClockGroup", "tasteClocks", "taste", "usage", "tasteAndUsage", "isBsAssortment", "isFsTsAssortment", "isFsAssortment", "isTsAssortment", "isTseAssortment", "isTsLsAssortment", "isTssAssortment", "isTstAssortment", "isTsvAssortment", "isPaAssortment", "hasAnyTasteClocks"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded GetProductData", {"tasteSymbolsList", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Values",{{"Systembolaget Produkt URL", type text}, {"information", type any}, {"productId", Int64.Type}, {"productNumberShort", Int64.Type}, {"assortmentText", type text}, {"drub", type text}, {"isNewVintage", type logical}, {"originLevel2", type any}, {"originLevel1", type any}, {"originLevel3", type any}, {"originLevel4", type any}, {"originLevel5", type any}, {"brandOrigin", type any}, {"bottleCode", type text}, {"bottleTypeGroup", type text}, {"seal", type any}, {"vatCode", Int64.Type}, {"priceExclVat", type number}, {"priorPrice", type any}, {"comparisonPrice", type number}, {"originalSellStartDate", type datetime}, {"productLaunchDate", type datetime}, {"productLaunchTime", type any}, {"producerName", type text}, {"production", type text}, {"cultivationArea", type any}, {"harvest", type any}, {"soil", type any}, {"supplierName", type text}, {"isSupplierTemporaryNotAvailable", type logical}, {"isSupplierNotAvailable", type logical}, {"backInStockAtSupplier", type any}, {"isDiscontinued", type logical}, {"isTemporaryOutOfStock", type logical}, {"isCompletelyOutOfStock", type logical}, {"isOutOfStockAtDepot", type logical}, {"restrictedParcelQuantity", type any}, {"isDefaultProduct", type logical}, {"isRegionalRestricted", type logical}, {"isLimitedEdition", type logical}, {"isHidden", type logical}, {"isSearchable", type logical}, {"isInAnyStoreSearchAssortment", type logical}, {"isStoreOrderApplicable", type logical}, {"isHomeOrderApplicable", type logical}, {"isAgentOrderApplicable", type logical}, {"seasonName", type any}, {"alcoholPercentage", Int64.Type}, {"tastingDate", type datetime}, {"tasteSymbols", type text}, {"additives", type any}, {"storage", type any}, {"preservable", type text}, {"hasInbounddeliveries", type logical}, {"isManufacturingCountry", type logical}, {"isGlutenFree", type logical}, {"isEthical", type logical}, {"ethicalLabel", type any}, {"isKosher", type logical}, {"isNaturalWine", type logical}, {"isVeganFriendly", type logical}, {"isInSelectedAssortment", type logical}, {"isEcoFriendlyPackage", type logical}, {"isDepotDelivered", type logical}, {"availableNumberOfStores", Int64.Type}, {"packaging", type text}, {"gtinPrimary", Int64.Type}, {"gtinSecondary", type any}, {"images", type any}, {"productNumber", Int64.Type}, {"productNameBold", type text}, {"productNameThin", type text}, {"priceInclVat", Int64.Type}, {"priceInclVatExclRecycleFee", Int64.Type}, {"isOrganic", type logical}, {"isLightWeightBottle", type logical}, {"volume", Int64.Type}, {"vintage", type any}, {"country", type text}, {"categoryLevel1", type text}, {"categoryLevel2", type text}, {"categoryLevel3", type any}, {"categoryLevel4", type any}, {"customCategoryTitle", type text}, {"recycleFee", Int64.Type}, {"recycleFeeIndicator", type text}, {"bottleTextShort", type text}, {"isWebLaunch", type logical}, {"isAddableToBasket", type logical}, {"productNutritionHeaders", type any}, {"allergens", type any}, {"ingredients", type text}, {"isDKI", type logical}, {"showAdditionalBsInformation", type logical}, {"color", type text}, {"aroma", type text}, {"additionalInformation", type text}, {"isProductLaunchDateHighlighted", type logical}, {"producerDescription", type text}, {"grapes", type any}, {"rawMaterial", type any}, {"sugarContent", Int64.Type}, {"tasteSymbolsList", type text}, {"isTasteAndUsageAlone", type logical}, {"hasAnyTaste", type logical}, {"hasRestrictions", type logical}, {"hasSymbols", type logical}, {"hasAnyTasteSymbols", type logical}, {"webLaunchStart", type datetime}, {"webLaunchEnd", type datetime}, {"priceExclVatExclRecycleFee", type number}, {"isDraughtRecycleFeeProduct", type logical}, {"isNews", type logical}, {"isNewInAssortment", type logical}, {"assortment", type text}, {"tasteClockGroup", type any}, {"tasteClocks", type any}, {"taste", type text}, {"usage", type text}, {"tasteAndUsage", type text}, {"isBsAssortment", type logical}, {"isFsTsAssortment", type logical}, {"isFsAssortment", type logical}, {"isTsAssortment", type logical}, {"isTseAssortment", type logical}, {"isTsLsAssortment", type logical}, {"isTssAssortment", type logical}, {"isTstAssortment", type logical}, {"isTsvAssortment", type logical}, {"isPaAssortment", type logical}, {"hasAnyTasteClocks", type logical}}),
    #"Expanded images" = Table.ExpandListColumn(#"Changed Type", "images"),
    #"Expanded images1" = Table.ExpandRecordColumn(#"Expanded images", "images", {"imageUrl"}, {"imageUrl"}),
    #"Expanded productNutritionHeaders" = Table.ExpandListColumn(#"Expanded images1", "productNutritionHeaders"),
    #"Expanded productNutritionHeaders1" = Table.ExpandRecordColumn(#"Expanded productNutritionHeaders", "productNutritionHeaders", {"productNutritionHeaderId", "productId", "preparationStateCode", "nutrientBasisQuantityTypeCode", "nutrientBasisQuantity", "measurementUnitCodeNameShort", "modified", "productNutritions"}, {"productNutritionHeaderId", "productId.1", "preparationStateCode", "nutrientBasisQuantityTypeCode", "nutrientBasisQuantity", "measurementUnitCodeNameShort", "modified", "productNutritions"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded productNutritionHeaders1",{"information"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "imageUrl2", each [imageUrl]&"_800.png"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Systembolaget Produkt URL", type text}, {"productId", Int64.Type}, {"productNumberShort", Int64.Type}, {"assortmentText", type text}, {"drub", type text}, {"isNewVintage", type logical}, {"originLevel2", type any}, {"originLevel1", type any}, {"originLevel3", type any}, {"originLevel4", type any}, {"originLevel5", type any}, {"brandOrigin", type any}, {"bottleCode", type text}, {"bottleTypeGroup", type text}, {"seal", type any}, {"vatCode", Int64.Type}, {"priceExclVat", type number}, {"priorPrice", type any}, {"comparisonPrice", type number}, {"originalSellStartDate", type datetime}, {"productLaunchDate", type datetime}, {"productLaunchTime", type any}, {"producerName", type text}, {"production", type text}, {"cultivationArea", type any}, {"harvest", type any}, {"soil", type any}, {"supplierName", type text}, {"isSupplierTemporaryNotAvailable", type logical}, {"isSupplierNotAvailable", type logical}, {"backInStockAtSupplier", type any}, {"isDiscontinued", type logical}, {"isTemporaryOutOfStock", type logical}, {"isCompletelyOutOfStock", type logical}, {"isOutOfStockAtDepot", type logical}, {"restrictedParcelQuantity", type any}, {"isDefaultProduct", type logical}, {"isRegionalRestricted", type logical}, {"isLimitedEdition", type logical}, {"isHidden", type logical}, {"isSearchable", type logical}, {"isInAnyStoreSearchAssortment", type logical}, {"isStoreOrderApplicable", type logical}, {"isHomeOrderApplicable", type logical}, {"isAgentOrderApplicable", type logical}, {"seasonName", type any}, {"alcoholPercentage", Int64.Type}, {"tastingDate", type datetime}, {"tasteSymbols", type text}, {"additives", type any}, {"storage", type any}, {"preservable", type text}, {"hasInbounddeliveries", type logical}, {"isManufacturingCountry", type logical}, {"isGlutenFree", type logical}, {"isEthical", type logical}, {"ethicalLabel", type any}, {"isKosher", type logical}, {"isNaturalWine", type logical}, {"isVeganFriendly", type logical}, {"isInSelectedAssortment", type logical}, {"isEcoFriendlyPackage", type logical}, {"isDepotDelivered", type logical}, {"availableNumberOfStores", Int64.Type}, {"packaging", type text}, {"gtinPrimary", Int64.Type}, {"gtinSecondary", type any}, {"imageUrl", type text}, {"productNumber", Int64.Type}, {"productNameBold", type text}, {"productNameThin", type text}, {"priceInclVat", Int64.Type}, {"priceInclVatExclRecycleFee", Int64.Type}, {"isOrganic", type logical}, {"isLightWeightBottle", type logical}, {"volume", Int64.Type}, {"vintage", type any}, {"country", type text}, {"categoryLevel1", type text}, {"categoryLevel2", type text}, {"categoryLevel3", type any}, {"categoryLevel4", type any}, {"customCategoryTitle", type text}, {"recycleFee", Int64.Type}, {"recycleFeeIndicator", type text}, {"bottleTextShort", type text}, {"isWebLaunch", type logical}, {"isAddableToBasket", type logical}, {"productNutritionHeaderId", Int64.Type}, {"productId.1", type any}, {"preparationStateCode", type any}, {"nutrientBasisQuantityTypeCode", type any}, {"nutrientBasisQuantity", Int64.Type}, {"measurementUnitCodeNameShort", type text}, {"modified", type datetime}, {"productNutritions", type any}, {"allergens", type any}, {"ingredients", type text}, {"isDKI", type logical}, {"showAdditionalBsInformation", type logical}, {"color", type text}, {"aroma", type text}, {"additionalInformation", type text}, {"isProductLaunchDateHighlighted", type logical}, {"producerDescription", type text}, {"grapes", type any}, {"rawMaterial", type any}, {"sugarContent", Int64.Type}, {"tasteSymbolsList", type text}, {"isTasteAndUsageAlone", type logical}, {"hasAnyTaste", type logical}, {"hasRestrictions", type logical}, {"hasSymbols", type logical}, {"hasAnyTasteSymbols", type logical}, {"webLaunchStart", type datetime}, {"webLaunchEnd", type datetime}, {"priceExclVatExclRecycleFee", type number}, {"isDraughtRecycleFeeProduct", type logical}, {"isNews", type logical}, {"isNewInAssortment", type logical}, {"assortment", type text}, {"tasteClockGroup", type any}, {"tasteClocks", type any}, {"taste", type text}, {"usage", type text}, {"tasteAndUsage", type text}, {"isBsAssortment", type logical}, {"isFsTsAssortment", type logical}, {"isFsAssortment", type logical}, {"isTsAssortment", type logical}, {"isTseAssortment", type logical}, {"isTsLsAssortment", type logical}, {"isTssAssortment", type logical}, {"isTstAssortment", type logical}, {"isTsvAssortment", type logical}, {"isPaAssortment", type logical}, {"hasAnyTasteClocks", type logical}, {"imageUrl2", type text}})
in
    #"Changed Type1"

[Anonymous]

Hi @Anonymous ,

Please try to replace the parameter in each GetProductData(  ) with Previous Applied Steps Name.

In my sample, it shoule be each GetProductData(#"Changed Type" )

If it does not make sense, please share the whole M code in Advanced Editor to help us clarify your scenario.

In addition, I have found a  similar thread here:

https://community.powerbi.com/t5/Power-Query/Expression-Error-We-cannot-convert-a-value-of-type-List-to-type/td-p/1430544/page/2

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.