Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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".
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"
@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?
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |