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