Unexpected error: Operation is not valid due to the current state of the object

[Bessonnizza]

Hello everyone

Try to invoke custom function:

let
data = Json.FromValue([UnitIds={UnitIds}]),
headers = [#"accept"="*/*", #"ScoutAuthorization"=ScoutAuthorizationId, #"Content-Type"="application/json"],
web = Web.Contents("http://url", [ Content = data, Headers = headers, ManualStatusHandling = {404, 400}]),
result = Json.Document(web)
in
    result 

 UnitIds and ScoutAuthoizationId are Parameters

When i try to invoke function like this (enter values), all is ok and a new column is created:

When i try to invoke function like this (choose columns), i get an error:

Error:

[Syndicate_Admin]

I hope this helps someone but in my case this was resolved after I apply the function "Text.From" once I called the column in the invoke function . Also as others I tried to invoke the function with static strings.

First I tested the funtion as it is:

=CustomInvokedFunction("Mexico","2023-06-30","Omar")

Then I tried to iterate for each row with also STATIC VALUES.

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction("Mexico","2023-06-30","Omar"))

Then try to call the column (this is when it was crashing):

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction([Site],[Date],[Name]))

Then I changed with the Text.From function (my solution):

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction(Text.From([Site]),Text.From([Date]), Text.From([Name])))

  I dont know why power by doesnt identify the column called as text. But for me this worked!!

[OmarTorres]

I hope this helps someone but in my case this was resolved after I apply the function "Text.From" once I called the column in the invoke function . Also as others I tried to invoke the function with static strings.

First I tested the funtion as it is:

=CustomInvokedFunction("Mexico","2023-06-30","Omar")

Then I tried to iterate for each row with also STATIC VALUES.

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction("Mexico","2023-06-30","Omar"))

Then try to call the column (this is when it was crashing):

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction([Site],[Date],[Name]))

Then I changed with the Text.From function (my solution):

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction(Text.From([Site]),Text.From([Date]), Text.From([Name])))

  I dont know why power by doesnt identify the column called as text. But for me this worked!!

[Baraner]

hi, same issue i have. I just closed the form1.cs tab(that one giving the warning), and reopen it with double click from solution explorer, wolaaa its opened without any error 🙂 

try to restart your computer too.. i think its about try to rendering something smoothly....

[aginnovatech]

I have the same error, if I invoke the function manually entering the parameters, it works wonderfully, but when a try invoking the column to iterate in each row, the same error appears

This is my custum fucntion:

(locations as text, start_date as datetime) as table=>
let
start_date = DateTime.ToText(start_date,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'", Culture="en-US"]),
Origen =
Json.Document(Web.Contents("https://api.arable.cloud/api/v2",
[RelativePath="data/hourly",Query=[pres="mb",temp="C",size="mm",vol="l",order="asc",location=locations,limit="9999",start_time=start_date]])),
#"Convertida en tabla" = Table.FromList(Origen, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Convertida en tabla",{}),
#"Se expandió Column1" = Table.ExpandRecordColumn(#"Tipo cambiado", "Column1", {"wind_speed_max", "ea", "location", "tdew_at_mint", "mint_time", "kc", "wind_speed", "prate", "rh_at_maxt", "tdew", "et", "precip_hours", "vpd", "gdd_cumulative_source", "long", "meant", "ndvi", "wind_speed_min", "wind_heading", "wind_direction", "cl", "max_tdew", "gdd", "crop_water_demand", "sunshine_duration", "min_rh", "swdw", "gdd_source", "mean_rh", "precip", "low_quality", "lat", "gdd_cumulative", "dli", "time", "lfairdelta", "maxt_time", "device", "mint", "sample_pct", "lfw", "slp", "mean_tbelow", "etc", "maxt", "rh_at_mint"}, {"wind_speed_max", "ea", "location", "tdew_at_mint", "mint_time", "kc", "wind_speed", "prate", "rh_at_maxt", "tdew", "et", "precip_hours", "vpd", "gdd_cumulative_source", "long", "meant", "ndvi", "wind_speed_min", "wind_heading", "wind_direction", "cl", "max_tdew", "gdd", "crop_water_demand", "sunshine_duration", "min_rh", "swdw", "gdd_source", "mean_rh", "precip", "low_quality", "lat", "gdd_cumulative", "dli", "time", "lfairdelta", "maxt_time", "device", "mint", "sample_pct", "lfw", "slp", "mean_tbelow", "etc", "maxt", "rh_at_mint"})
in
#"Se expandió Column1"

And this where I invoke the function:

let
 
    
    Origen = Json.Document(Web.Contents("https://api.arable.cloud/api/v2",
        [RelativePath="teams/61678d2b7fdc2e0015b270d5/locations",Query=[limit="10000"]])),
    #"Convertida en tabla" = Table.FromRecords({Origen}),
    #"Se expandió items" = Table.ExpandListColumn(#"Convertida en tabla", "items"),
    #"Se expandió items1" = Table.ExpandRecordColumn(#"Se expandió items", "items", {"current_device", "id", "org_id", "name", "gps", "gps_precision", "elev", "country", "county", "addr_city", "addr_state", "addr_postcode", "tags", "time_offset", "start_date", "end_date", "device_name", "radius", "state", "tz_name", "archived", "sites"}, {"items.current_device", "items.id", "items.org_id", "items.name", "items.gps", "items.gps_precision", "items.elev", "items.country", "items.county", "items.addr_city", "items.addr_state", "items.addr_postcode", "items.tags", "items.time_offset", "items.start_date", "items.end_date", "items.device_name", "items.radius", "items.state", "items.tz_name", "items.archived", "items.sites"}),
    #"Se expandió items.current_device" = Table.ExpandRecordColumn(#"Se expandió items1", "items.current_device", {"sensors", "id", "org_id", "owner_id", "name", "firmware_id", "reported_fw", "last_seen", "last_post", "last_deploy", "state", "model", "sync_interval", "batt_pct", "batt_volt", "signal_strength", "flags", "has_bridge"}, {"items.current_device.sensors", "items.current_device.id", "items.current_device.org_id", "items.current_device.owner_id", "items.current_device.name", "items.current_device.firmware_id", "items.current_device.reported_fw", "items.current_device.last_seen", "items.current_device.last_post", "items.current_device.last_deploy", "items.current_device.state", "items.current_device.model", "items.current_device.sync_interval", "items.current_device.batt_pct", "items.current_device.batt_volt", "items.current_device.signal_strength", "items.current_device.flags", "items.current_device.has_bridge"}),
    #"Dividir columna" = Table.SplitColumn(#"Se expandió items.current_device", "items.gps", each _, {"items.gps.0", "items.gps.1"}),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Dividir columna",{{"page", Int64.Type}, {"pages", Int64.Type}, {"limit", Int64.Type}, {"total", Int64.Type}, {"items.current_device.sensors", type any}, {"items.current_device.id", type text}, {"items.current_device.org_id", type text}, {"items.current_device.owner_id", type any}, {"items.current_device.name", type text}, {"items.current_device.firmware_id", type text}, {"items.current_device.reported_fw", type text}, {"items.current_device.last_seen", type datetime}, {"items.current_device.last_post", type datetime}, {"items.current_device.last_deploy", type datetime}, {"items.current_device.state", type text}, {"items.current_device.model", Int64.Type}, {"items.current_device.sync_interval", Int64.Type}, {"items.current_device.batt_pct", type number}, {"items.current_device.batt_volt", Int64.Type}, {"items.current_device.signal_strength", type text}, {"items.current_device.flags", type any}, {"items.current_device.has_bridge", type logical}, {"items.id", type text}, {"items.org_id", type text}, {"items.name", type text}, {"items.gps.0", type number}, {"items.gps.1", type number}, {"items.gps_precision", type number}, {"items.elev", type number}, {"items.country", type text}, {"items.county", type text}, {"items.addr_city", type text}, {"items.addr_state", type text}, {"items.addr_postcode", type any}, {"items.tags", type any}, {"items.time_offset", Int64.Type}, {"items.start_date", type datetime}, {"items.end_date", type any}, {"items.device_name", type text}, {"items.radius", Int64.Type}, {"items.state", type text}, {"items.tz_name", type text}, {"items.archived", type logical}, {"items.sites", type any}}),
    #"Duplicados quitados" = Table.Distinct(#"Tipo cambiado", {"items.id"}),
    #"Otras columnas quitadas" = Table.SelectColumns(#"Duplicados quitados",{"items.id", "items.name", "items.start_date"}),
    #"Columna condicional agregada" = Table.AddColumn(#"Otras columnas quitadas", "Personalizado", each if [items.id] = "616831234e1e73000dd7ef01" then "LOS REYES" else if [items.id] = "6168574ee2d7ac000a38f673" then "HDA. CAFETALERA" else if [items.id] = "61d32eca655f54000e4c280d" then "FINCA SAN CARLOS" else if [items.id] = "61d372059e7ce4000d998a44" then "LEÓN CORTÉS" else if [items.id] = "61d48d68655f54000c8bb2cf" then "SA DE CORRALILLO" else if [items.id] = "61d760ac9e7ce4000d9c27f2" then "San Lorenzo" else [items.name]),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columna condicional agregada",{{"Personalizado", "Localización ajustada"}}),
    #"Filas filtradas" = Table.SelectRows(#"Columnas con nombre cambiado", each [items.id] = "616831234e1e73000dd7ef01" or [items.id] = "6168574ee2d7ac000a38f673" or [items.id] = "61d32eca655f54000e4c280d" or [items.id] = "61d372059e7ce4000d998a44" or [items.id] = "61d48d68655f54000c8bb2cf" or [items.id] = "61d760ac9e7ce4000d9c27f2"),
    
    #"Added Custom" = Table.AddColumn(#"Filas filtradas", "DaysBetween", each DateTime.LocalNow() - [items.start_date]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"DaysBetween", type number}}),
    
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Ciclo", each Number.RoundUp(Value.Divide([DaysBetween],100))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "start-fecharoma", each List.DateTimes([items.start_date], [Ciclo], #duration(100, 0, 0, 0))),

    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom2", "start-fecharoma"),
    #"Invoked Custom Function" = Table.AddColumn(#"Expanded {0}", "GetLocationHourly", each GetLocationHourly([items.id], [#"start-fecharoma"]))
in
    #"Invoked Custom Function"

Last row is the one

[v-alq-msft · Accepted solution]

Hi, @Bessonnizza 

I'd like to suggest you check if the data source's status is ok. The error message usually means the file is corrupted. You may try to start from scratch after you have checked the data source. If it still doesn't work, please open a support ticket here .

Here is a similar thread you may refer to.

Best Regards

Allan

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