Cannot convert value to type table

Hello

I'm trying to split a string into multiple columns and I found out this code:

 SplitByDelimiter = (table, column, delimiter) =>
        let
            Count = List.Count(List.Select(Text.ToList(Table.Column(table, column){0}), each _ = delimiter)) + 1,
            Names = List.Transform(List.Numbers(1, Count), each column & "." & Text.From(_)),
            Types = List.Transform(Names, each {_, type text}),
            Split = Table.SplitColumn(table, column, Splitter.SplitTextByDelimiter(delimiter), Names),
            Typed = Table.TransformColumnTypes(Split, Types)
        in
            Typed, 

    #"Split Column by Delimiter" = SplitByDelimiter("My table", "my column", " ")

It seems a good candidate to test, but I'm getting the following error:

Expression.Error: We cannot convert the value "My Table" to type Table.
Details:
    Value=My Table
    Type=Type

My orginal column of type text is something like:

A B C D

B C

A B C D E

Since I do not know very well how to debug power query I'm a bit stuck.

Thanks

Need Help

MarcelBeug

8 years ago

Somehow you need to determine how many coluns you need for the entry with the largest number of delimiters.

One way is to have a "trial split" and count the number of items, as in the code below (with adjusted "Count" step).

let
    SplitByDelimiter = (table, column, delimiter) =>
        let
            Count = List.Max(List.Transform(Table.Column(table, column), each List.Count(Text.Split(_,delimiter)))),
            Names = List.Transform(List.Numbers(1, Count), each column & "." & Text.From(_)),
            Types = List.Transform(Names, each {_, type text}),
            Split = Table.SplitColumn(table, column, Splitter.SplitTextByDelimiter(delimiter), Names),
            Typed = Table.TransformColumnTypes(Split, Types)
        in
            Typed, 

    MyTable = #table(type table[my column = text],{{"A B C D"},{"A"},{"B C"},{"A B C D E"}}),
    #"Split Column by Delimiter" = SplitByDelimiter(MyTable, "my column", " ")
in
    #"Split Column by Delimiter"

Forum Discussion

Cannot convert value to type table

9 Replies

Related content

Snowflake connector: "We cannot convert the value null to type Text"

[Expression.Error] cannot convert null value to Logical

Converting [List] to text value

Converting to Percentage

Cannot convert value to type table