Help with query for multiple values within same URL

[rmacquha]

Hello,

The following code I am using to query VIN values for a site, works fine if I input the values as below (VINsToQuery) but not if I reference a list (separate table) with multiple values:

let
// Define a list of VINs to query
VINsToQuery = {"5UXWX7C5*BA","5UXWX7C5*BA"},

// Define a function to fetch content for a single VIN
GetContentForVIN = (vin) =>
let
// Construct the URL for the VIN
apiUrl = "https://vpic.nhtsa.dot.gov/api/vehicles/decodevinvalues/" & vin & "?format=xml&modelyear=2011",
// Fetch the content for the VIN
content = Web.Contents(apiUrl),
// Convert the content to a table
xmlTable = Xml.Tables(content)
in
xmlTable,

// Initialize an empty table to store the results
ResultTable = Table.FromRecords({}),

// Iterate through the list of VINs and fetch content for each
FetchResults = List.Accumulate(
VINsToQuery,
ResultTable,
(table, vin) => Table.Combine({table, Table.FromRecords({[VIN = vin, Content = GetContentForVIN(vin)]})})
),
#"Expanded Content" = Table.ExpandTableColumn(FetchResults, "Content", {"Count", "Message", "SearchCriteria", "Results"}, {"Content.Count", "Content.Message", "Content.SearchCriteria", "Content.Results"}),
#"Expanded Content.Results" = Table.ExpandTableColumn(#"Expanded Content", "Content.Results", {"DecodedVINValues"}, {"Content.Results.DecodedVINValues"}),
#"Expanded Content.Results.DecodedVINValues" = Table.ExpandTableColumn(#"Expanded Content.Results", "Content.Results.DecodedVINValues", {"MakeID", "ModelID", "ManufacturerId", "VIN", "BatteryInfo", "BatteryType", "BedType", "BodyCabType", "BodyClass", "EngineCylinders", "DestinationMarket", "DisplacementCC", "DisplacementCI", "DisplacementL", "Doors", "DriveType", "DriverAssist", "EngineCycles", "EngineModel", "EngineKW", "EntertainmentSystem", "FuelTypePrimary", "GVWR", "Make", "Manufacturer", "Model", "ModelYear", "PlantCity", "Seats", "Series", "SteeringLocation", "TransmissionStyle", "Trim", "VehicleType", "Windows", "Axles", "BrakeSystemType", "BatteryCells", "BedLengthIN", "BrakeSystemDesc", "CurbWeightLB", "AirBagLocCurtain", "AirBagLocSeatCushion", "BatteryA", "BatteryV", "BatteryKWh", "WheelBaseType", "SeatRows", "ValveTrainDesign", "TransmissionSpeeds", "EngineConfiguration", "AirBagLocFront", "FuelTypeSecondary", "FuelInjectionType", "AirBagLocKnee", "EngineHP", "EVDriveUnit", "PlantCountry", "PlantCompanyName", "PlantState", "Pretensioner", "SeatBeltsAll", "AdaptiveCruiseControl", "AdaptiveHeadlights", "ABS", "CIB", "BlindSpotMon", "NCSABodyType", "NCSAMake", "NCSAModel", "ESC", "TractionControl", "ForwardCollisionWarning", "LaneDepartureWarning", "LaneKeepSystem", "RearVisibilitySystem", "ParkAssist", "AirBagLocSide", "Trim2", "Series2", "WheelBaseShort", "WheelBaseLong", "Note", "Wheels", "TrailerType", "TrailerBodyType", "TrailerLength", "WheelSizeFront", "WheelSizeRear", "OtherRestraintSystemInfo", "CoolingType", "EngineHP_to", "ElectrificationLevel", "ChargerLevel", "ChargerPowerKW", "OtherEngineInfo", "BatteryA_to", "BatteryV_to", "BatteryKWh_to", "Turbo", "BasePrice", "BatteryModules", "BatteryPacks", "TopSpeedMPH", "SuggestedVIN", "ErrorCode", "PossibleValues", "AxleConfiguration", "EngineManufacturer", "BusLength", "BusFloorConfigType", "BusType", "OtherBusInfo", "CustomMotorcycleType", "MotorcycleSuspensionType", "MotorcycleChassisType", "OtherMotorcycleInfo", "OtherTrailerInfo", "AdditionalErrorText", "CashForClunkers", "TrackWidth", "TPMS", "ActiveSafetySysNote", "DynamicBrakeSupport", "PedestrianAutomaticEmergencyBraking", "AutoReverseSystem", "AutomaticPedestrianAlertingSound", "CAN_AACN", "EDR", "KeylessIgnition", "DaytimeRunningLight", "LowerBeamHeadlampLightSource", "SemiautomaticHeadlampBeamSwitching", "AdaptiveDrivingBeam", "SAEAutomationLevel", "SAEAutomationLevel_to", "RearCrossTrafficAlert", "GCWR", "GCWR_to", "NCSANote", "NCSAMappingException", "NCSAMapExcApprovedOn", "NCSAMapExcApprovedBy", "GVWR_to", "ErrorText", "RearAutomaticEmergencyBraking", "BlindSpotIntervention", "LaneCenteringAssistance", "NonLandUse", "VehicleDescriptor"}, {"Content.Results.DecodedVINValues.MakeID", "Content.Results.DecodedVINValues.ModelID", "Content.Results.DecodedVINValues.ManufacturerId", "Content.Results.DecodedVINValues.VIN", "Content.Results.DecodedVINValues.BatteryInfo", "Content.Results.DecodedVINValues.BatteryType", "Content.Results.DecodedVINValues.BedType", "Content.Results.DecodedVINValues.BodyCabType", "Content.Results.DecodedVINValues.BodyClass", "Content.Results.DecodedVINValues.EngineCylinders", "Content.Results.DecodedVINValues.DestinationMarket", "Content.Results.DecodedVINValues.DisplacementCC", "Content.Results.DecodedVINValues.DisplacementCI", "Content.Results.DecodedVINValues.DisplacementL", "Content.Results.DecodedVINValues.Doors", "Content.Results.DecodedVINValues.DriveType", "Content.Results.DecodedVINValues.DriverAssist", "Content.Results.DecodedVINValues.EngineCycles", "Content.Results.DecodedVINValues.EngineModel", "Content.Results.DecodedVINValues.EngineKW", "Content.Results.DecodedVINValues.EntertainmentSystem", "Content.Results.DecodedVINValues.FuelTypePrimary", "Content.Results.DecodedVINValues.GVWR", "Content.Results.DecodedVINValues.Make", "Content.Results.DecodedVINValues.Manufacturer", "Content.Results.DecodedVINValues.Model", "Content.Results.DecodedVINValues.ModelYear", "Content.Results.DecodedVINValues.PlantCity", "Content.Results.DecodedVINValues.Seats", "Content.Results.DecodedVINValues.Series", "Content.Results.DecodedVINValues.SteeringLocation", "Content.Results.DecodedVINValues.TransmissionStyle", "Content.Results.DecodedVINValues.Trim", "Content.Results.DecodedVINValues.VehicleType", "Content.Results.DecodedVINValues.Windows", "Content.Results.DecodedVINValues.Axles", "Content.Results.DecodedVINValues.BrakeSystemType", "Content.Results.DecodedVINValues.BatteryCells", "Content.Results.DecodedVINValues.BedLengthIN", "Content.Results.DecodedVINValues.BrakeSystemDesc", "Content.Results.DecodedVINValues.CurbWeightLB", "Content.Results.DecodedVINValues.AirBagLocCurtain", "Content.Results.DecodedVINValues.AirBagLocSeatCushion", "Content.Results.DecodedVINValues.BatteryA", "Content.Results.DecodedVINValues.BatteryV", "Content.Results.DecodedVINValues.BatteryKWh", "Content.Results.DecodedVINValues.WheelBaseType", "Content.Results.DecodedVINValues.SeatRows", "Content.Results.DecodedVINValues.ValveTrainDesign", "Content.Results.DecodedVINValues.TransmissionSpeeds", "Content.Results.DecodedVINValues.EngineConfiguration", "Content.Results.DecodedVINValues.AirBagLocFront", "Content.Results.DecodedVINValues.FuelTypeSecondary", "Content.Results.DecodedVINValues.FuelInjectionType", "Content.Results.DecodedVINValues.AirBagLocKnee", "Content.Results.DecodedVINValues.EngineHP", "Content.Results.DecodedVINValues.EVDriveUnit", "Content.Results.DecodedVINValues.PlantCountry", "Content.Results.DecodedVINValues.PlantCompanyName", "Content.Results.DecodedVINValues.PlantState", "Content.Results.DecodedVINValues.Pretensioner", "Content.Results.DecodedVINValues.SeatBeltsAll", "Content.Results.DecodedVINValues.AdaptiveCruiseControl", "Content.Results.DecodedVINValues.AdaptiveHeadlights", "Content.Results.DecodedVINValues.ABS", "Content.Results.DecodedVINValues.CIB", "Content.Results.DecodedVINValues.BlindSpotMon", "Content.Results.DecodedVINValues.NCSABodyType", "Content.Results.DecodedVINValues.NCSAMake", "Content.Results.DecodedVINValues.NCSAModel", "Content.Results.DecodedVINValues.ESC", "Content.Results.DecodedVINValues.TractionControl", "Content.Results.DecodedVINValues.ForwardCollisionWarning", "Content.Results.DecodedVINValues.LaneDepartureWarning", "Content.Results.DecodedVINValues.LaneKeepSystem", "Content.Results.DecodedVINValues.RearVisibilitySystem", "Content.Results.DecodedVINValues.ParkAssist", "Content.Results.DecodedVINValues.AirBagLocSide", "Content.Results.DecodedVINValues.Trim2", "Content.Results.DecodedVINValues.Series2", "Content.Results.DecodedVINValues.WheelBaseShort", "Content.Results.DecodedVINValues.WheelBaseLong", "Content.Results.DecodedVINValues.Note", "Content.Results.DecodedVINValues.Wheels", "Content.Results.DecodedVINValues.TrailerType", "Content.Results.DecodedVINValues.TrailerBodyType", "Content.Results.DecodedVINValues.TrailerLength", "Content.Results.DecodedVINValues.WheelSizeFront", "Content.Results.DecodedVINValues.WheelSizeRear", "Content.Results.DecodedVINValues.OtherRestraintSystemInfo", "Content.Results.DecodedVINValues.CoolingType", "Content.Results.DecodedVINValues.EngineHP_to", "Content.Results.DecodedVINValues.ElectrificationLevel", "Content.Results.DecodedVINValues.ChargerLevel", "Content.Results.DecodedVINValues.ChargerPowerKW", "Content.Results.DecodedVINValues.OtherEngineInfo", "Content.Results.DecodedVINValues.BatteryA_to", "Content.Results.DecodedVINValues.BatteryV_to", "Content.Results.DecodedVINValues.BatteryKWh_to", "Content.Results.DecodedVINValues.Turbo", "Content.Results.DecodedVINValues.BasePrice", "Content.Results.DecodedVINValues.BatteryModules", "Content.Results.DecodedVINValues.BatteryPacks", "Content.Results.DecodedVINValues.TopSpeedMPH", "Content.Results.DecodedVINValues.SuggestedVIN", "Content.Results.DecodedVINValues.ErrorCode", "Content.Results.DecodedVINValues.PossibleValues", "Content.Results.DecodedVINValues.AxleConfiguration", "Content.Results.DecodedVINValues.EngineManufacturer", "Content.Results.DecodedVINValues.BusLength", "Content.Results.DecodedVINValues.BusFloorConfigType", "Content.Results.DecodedVINValues.BusType", "Content.Results.DecodedVINValues.OtherBusInfo", "Content.Results.DecodedVINValues.CustomMotorcycleType", "Content.Results.DecodedVINValues.MotorcycleSuspensionType", "Content.Results.DecodedVINValues.MotorcycleChassisType", "Content.Results.DecodedVINValues.OtherMotorcycleInfo", "Content.Results.DecodedVINValues.OtherTrailerInfo", "Content.Results.DecodedVINValues.AdditionalErrorText", "Content.Results.DecodedVINValues.CashForClunkers", "Content.Results.DecodedVINValues.TrackWidth", "Content.Results.DecodedVINValues.TPMS", "Content.Results.DecodedVINValues.ActiveSafetySysNote", "Content.Results.DecodedVINValues.DynamicBrakeSupport", "Content.Results.DecodedVINValues.PedestrianAutomaticEmergencyBraking", "Content.Results.DecodedVINValues.AutoReverseSystem", "Content.Results.DecodedVINValues.AutomaticPedestrianAlertingSound", "Content.Results.DecodedVINValues.CAN_AACN", "Content.Results.DecodedVINValues.EDR", "Content.Results.DecodedVINValues.KeylessIgnition", "Content.Results.DecodedVINValues.DaytimeRunningLight", "Content.Results.DecodedVINValues.LowerBeamHeadlampLightSource", "Content.Results.DecodedVINValues.SemiautomaticHeadlampBeamSwitching", "Content.Results.DecodedVINValues.AdaptiveDrivingBeam", "Content.Results.DecodedVINValues.SAEAutomationLevel", "Content.Results.DecodedVINValues.SAEAutomationLevel_to", "Content.Results.DecodedVINValues.RearCrossTrafficAlert", "Content.Results.DecodedVINValues.GCWR", "Content.Results.DecodedVINValues.GCWR_to", "Content.Results.DecodedVINValues.NCSANote", "Content.Results.DecodedVINValues.NCSAMappingException", "Content.Results.DecodedVINValues.NCSAMapExcApprovedOn", "Content.Results.DecodedVINValues.NCSAMapExcApprovedBy", "Content.Results.DecodedVINValues.GVWR_to", "Content.Results.DecodedVINValues.ErrorText", "Content.Results.DecodedVINValues.RearAutomaticEmergencyBraking", "Content.Results.DecodedVINValues.BlindSpotIntervention", "Content.Results.DecodedVINValues.LaneCenteringAssistance", "Content.Results.DecodedVINValues.NonLandUse", "Content.Results.DecodedVINValues.VehicleDescriptor"})
in
#"Expanded Content.Results.DecodedVINValues"

[Anonymous]

Hi @rmacquha ,

Assuming you have a table named VINsTable with a column named VIN.

Please try modify the code as follows:

let
    // Reference the VINsTable with a column named VIN
    VINsTable = ...,

    // Extract the VINs from the VINsTable
    VINsToQuery = List.Transform(VINsTable[VIN], each Text.Trim(_)),

    // ... (rest of the code remains unchanged)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum