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