Expression.error: converting value to list

[IltisGerry]

Hi,

I'm pretty new to Power Query so I apologize in advance for my basic question.

What I want to do:

- load csv

- filter two columns, so that only relevant times remain (column1 < TIME < column2)

- find the maximum of another column (from the reduced/filtered table)

- write that maximum to another table

- what I (ultimately) want to to: have a function with input TIME. Then I supply a table "TABLE" with rows of times. For each of these rows, I want to call my function, which determines the maximum, and write that maximum to a second column next to "TABLE"

My problem:

- i get the Expression.Error: cannot convert value "7.5" to type "list"

My (cropped) code:

let
    Quelle = Csv.Document(File.Contents("XYZ.csv"),[Delimiter=",", Columns=8, Encoding=20269, QuoteStyle=QuoteStyle.None]),
    //some irrelevant stuff
    Filter1 = Table.SelectRows(data, each [Column1] < #time(16,30,0)),
    Filter2 = Table.SelectRows(Filter1, each [Column2] > #time(16,30,0)),

    Test1 = Table.Max(Filter2 ","RelevantValue"),
    Test2 = Test1[RelevantValue],
    Test3 = #table({"HereComesTheError"},{Test2}),
in
    Test3

Thank you in advance for your help.

Kind regard!

[v-juanli-msft · Accepted solution]

Hi @IltisGerry 

Right click on the table name, select "create function",

then edit queries in Advanced editor,

(time ) => let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdHRCcAgDATQXfItmJhWG1cp7r9GK0jTcqHgx4EPzZHzJM2SC4tRIuHO88xoHmmkL5PnrrwiMI1eU2DVWe264gas+U8+5g7MotnqYuW/aQMWNj2AhRUMWFhBGFzYQe49jAs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, time1 = _t, time2 = _t, value = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"time1", type time}, {"time2", type time}, {"value", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [time1] < time),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [time2] > time),
        #"Calculated Maximum" = List.Max(#"Filtered Rows1"[value]),
        #"Converted to Table" = #table(1, {{#"Calculated Maximum"}})
    in
        #"Converted to Table"

Then enter time in the format "hh:mm:ss" and invoke, you will get another query.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[artemus]

I'm not sure exactly what you are trying to do, but the table constructor #table, second argument is a list of lists, and you have only provided a list with a single element, the value.

What it looks like you want to do is simply:

...
Test1 = List.Max(Filter2[RelevantValue]),
Test2 = Table.AddColumn(Filter2, "MaxTime", each Test1, type time)