Forum Discussion
M Query filter on Value.FromText
If you want the results to be numeric, you need to adjust the data type to number (or whole number: Int64,Type).
Below some code with a few examples.
let
Source = #table(type table[Account Number = text],{{"0000400099"},{"0000400100"},{"0000400101"},{"0000400102"}}),
#"Filtered Rows" = Table.SelectRows(Source, each Value.FromText([Account Number]) >= 400100),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "ValueFromText", each Value.FromText([Account Number])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "NumberFrom", each Number.From([Account Number])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "Account Number", "Account Number - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Account Number - Copy", Int64.Type}, {"ValueFromText", Int64.Type}, {"NumberFrom", Int64.Type}})
in
#"Changed Type"
That's the rub, I don't want (can't) have them be numeric. They're used in a hierarchy joining with another table. For the relationship and hierarchy to work, they need to remain text.
Your code is working fine with me and I can load the table without any issues.
Can you eloborate on "it fails": don't you get the desired results? Or what error message do you get?
Not sure what the error is exactly. I insert the line to filter, Close and Apply, then this:
Try to view the error, but there doesn't seem to be any info:
I backtrack through the query steps of the error table to where there is data, and supposed errors, but the columns all appear clean to me. I've seen errors in other queries/projects with the little orange Error word.
Strangely, I can just close the error popup and the accounts seem to be filtered, but then my measures and visuals break.
The measure that supposedly has the error does use Account Number, but not in the SUM portion. Very strange stuff....
This phenomenon - errors with an empty error list - typically indicates that you have values in your table that conflict with the column type (e.g. numbers in a text column). Power Query doesn't regard this as errors (that's why the list is empty), but errors are reported when loading the data.
Otherwise it is strange that you get the error with filtered data and not with the complete set.
Maybe you can try and remove the filter to double check if the error is gone?
It can be a coincidence that your data was updated while you adjusted the query, and the error was not due to applying the filter, but to updated data.
That will be all from me today.