Forum Discussion
An error occurred in the ‘FirstQual’ query. Expression.Error
Hi BennyA ,
The error you are getting is because you have passed too many arguments to the Table.TransformColumnTypes function. This function only accepts two or three arguments: a table, a list of column names and types, and optionally a culture value. You have passed eight arguments, which is invalid.
To fix the error, you need to change the second line of your code to something like this:
#"ChangedType" = Table.TransformColumnTypes(Source, {{"EDIPI", Int64.Type}, {"TMS", type text}, {"RatingAtCI", type text}, {"PGatCI", type text}, {"AMEXatCI", type text}, {"QualType", type text}, {"QualDate", type date}, {"TTQ_Months", Int64.Type}}),
Notice that I have changed the type of QualDate column from text to date, since you are using List.Min function on it later. Also, I have removed the extra commas and brackets that were causing syntax errors.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Neeko
Thank you for taking the time to respond. I made the changes you suggested and I am now getting the following error:
An error occurred in the ‘MinQualDate’ query. Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]
Here is the modified code with your changes. I hope you can spot what I have done wrong.
= () => let
Source = TTQ,
#"ChangedType" =
Table.TransformColumnTypes(Source, {{"EDIPI", Int64.Type}, {"TMS", type text}, {"RatingAtCI", type text},
{"PGatCI", type text}, {"AMEXatCI", type text}, {"QualType", type text}, {"QualDate", type date},
{"TTQ_Months", Int64.Type}}),
#"SortedRows" =
Table.Sort(#"ChangedType", {{"EDIPI", Order.Ascending}, {"TMS", Order.Ascending},
{"QualType", Order.Ascending}, {"QualDate", Order.Ascending}}),
#"GroupedRows" =
Table.Group(#"SortedRows", {"EDIPI", "TMS", "QualType"}, {{"FirstQualDate", each
List.Min{[QualDate]}, type date}}),
#"MergedQueries" =
Table.NestedJoin(#"SortedRows", "GroupedRows", {"EDIPI", "TMS", "QualType", "QualDate"}, {"EDIPI",
"TMS", "QualType", "FirstQualDate"}, ({"FirstQualType"}), JoinKind.LeftOuter),
#"RemovedColumns" =
Table.RemoveColumns(#"MergedQueries", {"FirstQualDate"}),
#"SortedRows1" =
Table.Sort(#"RemovedColumns", {{"EDIPI", Order.Ascending}, {"TMS", Order.Ascending}, {"QualType",
Order.Ascending}, {"QualDate", Order.Ascending}})
in
SortedRows1