Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
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)