Forum Discussion
Expression.error: converting value to list
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!
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.
2 Replies
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)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.