Forum Discussion
Cannot convert value to type table
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"
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 |
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"