M Query filter on Value.FromText

[Aron_Moore]

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]

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"

[Aron_Moore]

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]

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?

[Aron_Moore]

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....

[MarcelBeug]

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.

[Aron_Moore]

Removing the filter does "fix" the error.

The data on the source system has not changed.

[v-ljerr-msft]

Hi @Aron_Moore,

It seems to be that the issue is related to the measures. Could you share a sample pbix file(with just some dummy data) which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[MarcelBeug]

It looks like the issue is in your measures; DAX is not really my area of knowledge.

Perhaps you have some incomplete releationships after filtering (1:m becomes 1:0/m or 0/1:m).

[Aron_Moore]

Working off your idea I recreated the pbix with just the one query/table and no calculated measures or columns.

I then try to apply my filter and get the error still.

Interestingly, I noticed the columns that are detected as aggregate lose their aggregation with the error applied.