Power Query: Custom Function to convert a single text comma delimited list input into a new list, "wrapped" in other text

Question

I'd appreciate help please. I'm trying to write my first function in Power Query (in Poweer BI, not Excel) which takes a text string as an input and outputs it "wrapped in other text".

The input is a comma separated single ie.

"GB02, NL01, IT05, FR03"

the desired output is:

"[Company].[GB02],[Company].[NL01],[Company].[IT05],[Company].[FR03]"

I've started by using the text split function whcih I've created a function called List2Table:

List2Table:

(InputText as text) =>
let 
SplitData = 
    
    Table.FromList(
        Text.Split(InputText,",")
        )

in SplitData

which when invoked...

= List2Table("GB02,NL01,IT05,FR03")

returns:

Column1
GB02
NL01
IT05
FR03

I now want to append the text

"[Company].["

....to the beginning of each value, and

"]"

....to the end, and then recombine into a single text string with each value separated by commas, but I dont know how to do this as part of the original function.

I've looked into this and I believe that this is achieved with the "each" operator, so I've tried this:

(InputText as text) =>
let 
SplitData = 
    
    Table.FromList(
        Text.Split(InputText,",")
        , each "[Company].[" & _ & "]")

in SplitData

but this gives the error:

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "[Company].[GB02]" to type List.
Details:
    Value=[Company].[GB02]
    Type=[Type]

Any help greatly received

Mike

Answer 1

You are showing your desired output as a text string, not a table.

To create you output string from the input, you can simply use the List.Transform function.

eg:

(InputText as text)=>

let 
    SplitData = Text.Split(InputText,","),
    Process = List.Transform(SplitData, each "[Company].[" & _ & "]")
in 
    Text.Combine(Process,",")

As invoked:

If you want something else, please be more specific.