M Query filter on Value.FromText

Question

So I have a column that I can't change to number but need to filter based on value.

I used the advanced editor to do this and it seems to work in preview, the "values" start where I want, but fails when I try to Close and Apply:

I've also tried to create a column that is the Value.From text and filter the custom column, which also fails. I am using Import Mode.

Any ideas?

marcelbeug · Answer

If you want the results to be numeric, you need to adjust the data type to number (or whole number: &nbsp;Int64,Type).&nbsp;Below some code with a few examples.&nbsp;let
    Source = #table(type table[Account Number = text],{{"0000400099"},{"0000400100"},{"0000400101"},{"0000400102"}}),
    #"Filtered Rows" = Table.SelectRows(Source, each Value.FromText([Account Number]) &gt;= 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"

aron_moore · Answer

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.

marcelbeug · Answer

Your code is working fine with me and I can load the table without any issues.&nbsp;Can you eloborate on "it fails": don't you get the desired results? Or what error message do you get?

aron_moore · Answer

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&nbsp;error table&nbsp;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.&nbsp;&nbsp;Strangely, I can just close the error popup and the accounts seem to be filtered, but then my measures and visuals break.&nbsp;The measure that supposedly has the error does use Account Number, but not in the SUM portion. Very strange stuff....

marcelbeug · Answer

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.&nbsp;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.&nbsp;That will be all from me today.

Forum Discussion

M Query filter on Value.FromText

9 Replies

Related content

filter by date on Odata query

Filter

merging direct query queries

OData query filter by results from another query

DAX Query help