Cannot convert value to type table

[Anonymous]

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

A

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

[Greg_Deckler]

I believe that you are showing a function "SplitByDelimiter" and a step in a query that calls that function. You need to substitute "My table" with the actual name of your table, no double quotes. Same thing with "my column".

[Anonymous]

Hello @Greg_Deckler

I tried. But my table is called My Table... so how can I call it? I tried no quotes, single quotes and always get an error.

Thanks

[Greg_Deckler]

Just rename your table.

[MarcelBeug]

The table name must be without quotes and the column name must be with quotes.

let
    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(MyTable, "my column", " ")
in
    #"Split Column by Delimiter"

This is working with me, but you should notice that it results in a table with the number of columns according to the value of the first row, i.c. 4, so the "E" on row 4 gets lost.

[Anonymous]

Hello @MarcelBeug and @Greg_Deckler

I just used the function with the suggested aproaches (no quotes, with quotes) and it work, or to say it correctly.. more or less,

Now I get an "Expression.Error: A cyclic reference was encountered during evaluation." error.

@MarcelBeughow can I "escape" the situation you describe? My table will have in most cases only has a value.

I tested on this one:

A1           B1

1	1
2	1 2
3	1 2 3 4
4	1 2 3 4 5 6
5	1
6	2 3 4
7	2

[MarcelBeug · Accepted solution]

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"

[Anonymous]

@MarcelBeugThanks.

I'm still getting the circular error... I have to use the formula in another table? Can I not do this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYq5EQAxCMRaYTbexDx2MQz9t3EHDpxJGmVigVhUGp3BjWJCu4kO22Ux8XF/LiH3j25D+6f3njZUfQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A1 = _t, B1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A1", Int64.Type}, {"B1", type text}}),

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(TabelaA, "B1", ",") 


in
    #"Split Column by Delimiter"

[MarcelBeug]

If you want to refer to the table inside the query, then you must refer to the appropriate step name inside the query, i.c. #"Changed Type", and not to the name of the query (I guess: you forgot to mention that your query is called "TabelaA"?).

[Anonymous]

@MarcelBeugMany thanks

Took a while to understand your words, but I got there. I was aware that we have to use the step name to make the chain of commands to work, but I was using the "programmer approach" to functions... and using the table name (query is this case), and I was getting the circular error.

this is my final code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIxBLwQjMNoawFYwVTMB8EwRfwVTBDCxmCtdpBmQh1JqDeEqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A1 = _t, B1 = _t]),

    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, 


    #"Split Column by Delimiter" = SplitByDelimiter(Source, "B1", " ")
in
    #"Split Column by Delimiter"

And yes, my query/table is TabelaA.

It works now.

Now for the second problem that is using a slicer to filter only the rows thta have a specific value. For example 4. Any clues?