Help with query for multiple values within same UR...

rmacquha · ‎09-12-2023

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 · ‎09-13-2023

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