Table.AddColumn type mismatch should throw error or warning.

[Moof]

Hi, 

Table.AddColumn appears to not enforce the column type, or convert the value to the required type.

I'll illustrate this with an example:

let
    Source = #table (
                        type table [Description = text],
                        {
                            {"To the right you should see 51, true, text"}
                        }
    ),
    #"Added Custom" = Table.AddColumn(Source, "Added Column", each 51, type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Value is Text", each Value.Type([Added Column]) = type text),
    Custom1 = Table.AddColumn(#"Added Custom1", "Schema", each Table.Schema(#"Added Custom1") ),
    #"Expanded Schema" = Table.ExpandTableColumn(Custom1, "Schema", {"Name", "Kind"}, {"Schema.Name", "Schema.Kind"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Schema", each ([Schema.Name] = "Added Column")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Schema.Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Schema.Kind", "Column type"}})
in
    #"Renamed Columns"

This returns a table where we see: 51, false, text instead of 51, true, text, as I'm putting a value of type number into a column of type text, and the convesion is not being made.

This is already mentioned in this other post where we are encouraged to read the language spec. So I did, and it is a bit convoluted, bu that is the way the language is designed. 

That being said, whilst the langueage specification does allow for a number value to be placed in a text column (or text field in a record), there is nothing to say that Table.AddColumn shouldn't raise an error, as it is a library function, not a language primitive, or at the very least a warning.

More specifically, when this is loaded into the model, the type mismatch will throw an error in the model, but that mashup engine won't show that error when I then click on show errors. As such, there is no way to know what happened, and that is a problem that needs solving.

The mashup engine should either throw an error in this instance, or some sort of warning, to aid debugging of this problem.

[Moof]

You can download an example file here: https://cofin1-my.sharepoint.com/:u:/g/personal/garadford_transcoma_com/ET6lWAjoTGBApeOAADC1YyQBlKwy... If you refresh, you will see the error.

[v-qiuyu-msft]

Hi @Moof,

In your report, you can see the Added Column type is TEXT which should align left, but the value 51 aligns right. So you need to add another step to transform the type to TEXT. 

You can modify the query below: 

let
    Source = #table (
                        type table [Description = text],
                        {
                            {"To the right you should see 51, true, text"}
                        }
    ),
    #"Added Custom" = Table.TransformColumnTypes(Table.AddColumn(Source, "Added Column", each 51, type text),{{"Added Column", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Value is Text", each Value.Type([Added Column]) = type text),
    Custom1 = Table.AddColumn(#"Added Custom1", "Schema", each Table.Schema(#"Added Custom1") ),
    #"Expanded Schema" = Table.ExpandTableColumn(Custom1, "Schema", {"Name", "Kind"}, {"Schema.Name", "Schema.Kind"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Schema", each ([Schema.Name] = "Added Column")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Schema.Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Schema.Kind", "Column type"}})
in
    #"Renamed Columns"

Best Regards,
Qiuyun Yu 

[Moof]

Hi Qiuyun, That does correct the deliberate mistake in my code. However, it does not address my issue. The Mashup Engine will not throw an error with my code. However the Tabular Model does throw an error. So when I exit the query editor and apply my queries to the model, I will be told there is an error. When I click on "Show Errors", I get taken back to the query editor and shown a query that shows the errors the Mashup Engine creates. As the Mashup engine does not throw an error, I get given a blank table with 0 errors on it. This makes it impossible to find out what is wrong, let alone debug it. Ideally, one of the following should happen: 1. Table.AddColumn should throw an error when I try to put a number into a text type column 2. Table.AddColumn should do an implicit type conversion, and throw an error if it cannot do that 3. The Tabular Model errors should be show in a separate screen to Mashup Engine errors What is not acceptable is that the Tabular Model error is silenced, and so we are unable to work out what the problem is.

[Moof]

I'm sorry, I can't seem to use rich text for formatting in the comments.

[Moof]

Hi Qiuyun,

That does correct the deliberate mistake in my code. However, it does not address my issue.

The Mashup Engine will not throw an error with my code. However the Tabular Model does throw an error. So when I exit the query editor and apply my queries to the model, I will be told there is an error. When I click on "Show Errors", I get taken back to the query editor and shown a query that shows the errors the Mashup Engine creates. As the Mashup engine does not throw an error, I get given a blank table with 0 errors on it. This makes it impossible to find out what is wrong, let alone debug it.

Ideally, one of the following should happen:

1. Table.AddColumn should throw an error when I try to put a number into a text type column
2. Table.AddColumn should do an implicit type conversion, and throw an error if it cannot do that
3. The Tabular Model errors should be show in a separate screen to Mashup Engine errors.

What is not acceptable is that the Tabular Model error is silenced, and so we are unable to work out what the problem is.

[v-qiuyu-msft]

Hi @Moof,

I have reported this issue internally: CRI 99463806, will update here once I get any information. 

Best Regards,
Qiuyun Yu

[Moof]

Thanks! @v-qiuyu-msft!

[v-qiuyu-msft]

Hi @Moof,

The product team already created a bug to track this issue. Please see below information from them:

"I agree that this behavior is confusing.

Eager type checking and validation doesn't fit with Power Query's lazy evaluation model, and would introduce a (potentially) large performance hit. Implicit type conversions can also lead to a number of other issues / unexpected behaviors. The M language provides explicit type converters that should be used whenever you want to ensure the data type (for example, Text.From()), and don't mind the overhead/performance implications of doing the type check/conversion.

For the third suggestion:

>> 3. The Tabular Model errors should be show in a separate screen to Mashup Engine errors.

I think this is the most viable solution to this problem. Whether the errors are shown in a separate window or not, there should be a way to see the exact rows that failed during import. We have had a backlog item tracking this internally (even though the issue doesn't seem highly voted on the Ideas forum). I've added this specific use case to the scenario. Hopefully it is something we can improve in the near future."

Best Regards,
Qiuyun Yu