Power Query missing type error

[greggyb]

It is possible to lie to Power Query about the type of a column and have phantom errors appear. This seems to be possible with any non-type-casting function that takes an output type as a parameter. A simple example is provided in the linked pbix. The relevant queries exist in code blocks at the bottom of this post.

Essentially, we can call

Table.TransformColumns(table, {{"column", function, <incorrect type>}})

This will cause the Power Query preview to show the icon for `<incorrect type>`. When we close and apply, the PBI Data Model will present an error upon loading the data, and offer to show errors. When we view the generated 'show errors' query, we will see no errors present, just like in the preview.

If we attempt to use the incorrectly typed field, we will see errors that occur after the erroneous type. This is at best confusing and inconsistent behavior.

If we call `Value.Type()` on the value whose type we have lied about, we get the correct type back. This indicates that Power Query should be able to have all the information necessary to throw a useful error earlier, when we first lie.

Queries are below. There are several, which show the various behaviors above.

//Query named #"Lying to Power BI"
let
    Source =
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText("i45WMlTSUTJSio0FAA==", BinaryEncoding.Base64)
                    ,Compression.Deflate)
                )
                ,let _t = ((type text) meta [Serialized.Text = true]) 
                in 
                    type table [Column1 = _t, Column2 = _t]
        )
    ,#"Changed Type" =
        Table.TransformColumnTypes(
            Source
            ,{
                {"Column1", Int64.Type}
                ,{"Column2", Int64.Type}
            }
        )

    // The query is unexceptional until this point. Here we call TransformColumns with
    // Text.From, this is fine, but then we lie and call the output type Int64.Type.
    // The output of this step in the PQ GUI shows with a column icon for whole number
    // (the '123' icon). The value in the field is left-aligned, as if a text value.
    // When we load the data to the data model, we get an error notification. When
    // selecting "Show Errors" the automatically generated query in the 'Query Errors - ...'
    // query group appears. I have not modified this query. We can see that there are
    // no errors presented in the M expression of this query, though we get the notification
    // in the data model GUI. 

    // It seems an error should be thrown in Table.TransformColumns().
    ,Test =
        Table.TransformColumns(
            #"Changed Type"
            ,{
                {"Column2", Text.From, Int64.Type/*LIES!*/}
            }
        )
in
    Test



//Query named #"But can't lie to M"
let
    Source = #"Lying to Power Query",
    Square =
        // Here we try to square a number which the PQ GUI tells us is a whole Number.
        // At this point, we do get an error in the M expression that bubbles up into
        // the PQ expression preview.
        Table.TransformColumns(
            Source
            ,{
                {"Column2", each Number.Power(_, 2), Int64.Type}
            }
        )
in
    Square



//Query named #"Really can't lie to M"
let
    Source = #"Lying to Power Query",
    AddedCustom =
        Table.AddColumn(
            Source
            ,"TypeOfColumn2"
            ,each Value.Type([Column2])
        )
    // Here note that the type of Column2, despite being indicated as a
    // whole number by the column icon '123', is returned as text. It seems
    // that there is enough information available for Power Query to be able
    // to properly indicate the error condition BEFORE applying the step.
    ,TypeOfColumn2 = AddedCustom{0}[TypeOfColumn2]
in
    TypeOfColumn2




//Query named #"Errors in Lying to Power BI"
let
Source = #"Lying to Power Query",
#"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1),
  #"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Column1", "Column2"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Column1", "Column2"})
in
  #"Reordered Columns"

[v-yuezhe-msft]

@greggyb,

I have reported this issue internally: CRI 94775900. Will post back once I get any updates.

Regards,
Lydia

[v-yuezhe-msft]

@greggyb,

A task has been created to track this issue.

Regards,
Lydia