Help with query for multiple values within same URL

rmacquha — Tue, 12 Sep 2023 16:01:06 GMT

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"

Re: Help with query for multiple values within same URL

Anonymous — Thu, 14 Sep 2023 03:04:45 GMT

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

topic Help with query for multiple values within same URL in Power Query

Help with query for multiple values within same URL

Re: Help with query for multiple values within same URL