View Full Version : Text.ToList - how to reverse this step

2016-05-06, 05:25 PM
Hi together,

I played around with the Text.ToList function.

Pls. see below:

Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Text", type text}}),
#"Hinzugefügter Index" = Table.AddIndexColumn(#"Geänderter Typ", "Index", 1, 1),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Hinzugefügter Index", "Custom", each Text.ToList ([Text])),
#"Erweiterte Custom" = Table.ExpandListColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Custom")
#"Erweiterte Custom"

The result for the Text "Ich" is:

Ich 1 I
Ich 1 c
Ich 1 h

How can I go the opposite way?
Coming from:

1 I
1 c
1 h

to Ich?

If someone has a hint - I would like to take it.


Ken Puls
2016-05-06, 05:59 PM
Hi Uwe,

I think I'd build a custom function for this purpose, like this:

(SourceList as list) =>

// SourceList = {"i","c","h"},
TempTable = Table.Transpose(Table.FromList(SourceList)),
Merged = Table.CombineColumns(TempTable,{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Value"),
Output = Merged[Value]{0}

You'd then pass your list into this as follows (provided it was called fxMergeListItems)

myList = {"i","c","h"},
output = fxMergeListItems(myList)

2016-05-06, 06:28 PM
Hi Ken,

thanks for the fast answer. I have to read Chapter 21 once again, as up to now I was not able to get the custom function under control.

I will open a new thread for this.


Ken Puls
2016-05-06, 07:28 PM
Hey Uwe,

No need to open a new thread. Let's continue on this this one.

What part was tripping you up in the custom function implementation?

2016-05-07, 11:01 AM
Hi Ken,

my problem was, that I needed it more easy to start with what should be the result of the function.
E.g. a simple: plus 1. Just to have a very short "function".

Also I was not able to figure out where to do what. Where to build the function. Where to name the function .How and where to call the function.

Another problem may be, that I like trying more than reading ;-)

But now I got it and train by repeating without "spying" and also with more variables and e.g. text.

The way I did it at the end was the following:

Build a small table in Excel:


Importing the table into PQ.
In the "function line" I see: = Table.TransformColumnTypes(Quelle,{{"myValue", Int64.Type}})

After importing just "close and load". Now in Ecxel I see again:


So far so good - and nothing new.

Now the 1st step for the first custom function:

Icon: from other source -> empty query (names can be different - I have a German version)

Now the cursor stands in the "function line".

Just type: = (x) => x+1
At this point don't try it out. (do not click invoke)

But please: on the right side -> change the name to the name the function should be named.

In my case: PlusOne

Go to start button and "close and load".

Now I/You see 2 things on the right side: One table and the function.

Now I/we can make them work together - the 2nd step for the first custom function.

Open the first Query with the table inside. Add a custom column and type behind the =

PlusOne ([myValue])

The result shoud look like:

myVale Custom

1 2
2 3
3 4

That was the way I finally did my first custom function.

And now I have to spend some time with your custom function Ken. Table, List, ...

Thanks again

Bill Szysz
2016-05-07, 11:33 PM
Maybe this way? :-)

Source = Table.FromColumns({{1,1,2,2,2,3,3,3,3,3,3,3,4,4,4,4},{"I"," ","a","m"," ","P","o","l","i","s","h"," ","g","u","y",":-)"}}, {"Idx","Characters"}),
Result = Table.Group(Source, {"Idx"}, {{"Text", each Text.Combine(_[Characters]), type text}})


2016-05-08, 08:23 PM
Hi Bill,

thanks for your help. With the things I've done in the last days ("read, try, ....") I was able to put your code at the right place.

And - it runs perfect.

Two small things I learned in addition.

"Source" is in the german version "Quelle". As Source/Quelle will be always automatically named, I haven't had it in mind while typing your code.
But solved it in app. 3 minutes.

I had a look at the Text.Combine function and added the " " space.

= Table.Group(Quelle, {"Idx"}, {{"Text", each Text.Combine(_[Characters]," "), type text}})

Bill, best greetings from sunny Germany to (i believe) sunny Poland. Thank you.

Now I will have a second look at Ken's way.