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