Power Query - Split Column by Parameters

[Anonymous]

Hi,

I want to be able to split a column by 2 parameters - one being the delimiting character, the other being the number of delimiters or columns to create. I'm creating a report template for use across different Clients. They'll have different naming conventions for Campaign Names, with different delimiters and different numbers of delimiters. I want to use parameters to make the set up easy when switching to other Clients.

I'm able to split a column by the character by amending the M Language after it's been split, as follows:

Table.SplitColumn(#"Added Data Source", "Taxonomy Field", Splitter.SplitTextByDelimiter(#"Taxonomy: CM360 Campaign Name - Delimiter", QuoteStyle.Csv), {"Taxonomy Field.1", "Taxonomy Field.2", "Taxonomy Field.3", "Taxonomy Field.4", "Taxonomy Field.5"})

However, I would like to be able to pass a parameter that tell Power Query how many columns to create. Can you tell me if this is possible? I can't see how to do it from the above code.

Thanks,

Mark

[Anonymous]

@Vijay_A_Verma  actually, I have one addition I'd like to make.

When it's splitting my columns by 4 delimiters, if there is a fifth, I'd like to see that in the final column. But at the moment it's only creating 4 columns and removing anything beyond the fourth.

Would it be possible to amend the Query to allow everything past the fourth delimiter to appear in the final column?

Thanks,

Mark

[Vijay_A_Verma · Accepted solution]

Replace this 

{"Taxonomy Field.1", "Taxonomy Field.2", "Taxonomy Field.3", "Taxonomy Field.4", "Taxonomy Field.5"}

with (where Param is your parameter)

List.Transform({1..Param}, each "Taxonomy Field."&Text.From(_))

[Anonymous]

@Vijay_A_Verma  Tremendous, that's working perfectly. Thanks!