Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

1 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"
Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

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".


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Just rename your table.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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

11
21 2
31 2 3 4
41 2 3 4 5 6
51
62 3 4
72

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"
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

@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"

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"?).

 

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

@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?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.