Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

aseagull

Power Query type-checking fails after editing custom column type

Submitted by Helper IV on ‎01-05-2023 02:35 PM

The following M code works, even though it should throw an error where I'm comparing a text value to a number. Everywhere else, it seems like M is very strict about types. Why is this allowed?

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslCK1YlWsgSThgYQylApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Col]+0, type text),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] >= 10)
in
    #"Filtered Rows"

 

 

Status: Needs Info
See more ideas labeled with:
2 Comments (2 New)
Comments
Anonymous
Not applicable
‎01-06-2023 12:44 AM

Hi @aseagull 

Can you provide a sample for us to test?

 

Best Regards,
Community Support Team _ Ailsa Tao

aseagull
Helper IV
Helper IV
‎01-08-2023 02:05 PM

Okay, the behavior is not as deterministic as I thought. The initial problem, connecting to an Azure SQL db generously provided by a leader in our local usergroup, allowed the numeric comparison to apply to a text column, and load the resultant data into Power BI (as text). 

 

For any other data source I've tried, including the blank query approach pasted above (you can paste the M into a blank query and it will work), Power Query allows the numeric comparison on the text data, but then Power BI reports an error on data load (and the column loads with no data).

 

  1. Open Power BI. Get Data. Blank Query.
  2. Paste this M into the Advanced Editor. Save.
  3. Observe M happily compares numbers and text.
  4. Close and apply.
  5. Errors result. No column data loaded.

So for the example I've provided, Power BI does the right thing by reporting an error. It's still curious that Power Query doesn't report an error when comparing a text value to a numeric literal, since it does every other time I've (accidentally) compared them. It will typically say:

Expression.Error: We cannot apply operator < to types Number and Text.

 

Idea Statuses