Table.SplitColumn with variables number of columns

[PietroFarias]

Hey guys, an aid on PowerQuery.
I use the Table.SplitColumn for split de Column. My query sometimes can have 5 columns or other times 7, or 9. Today I default 9 columns in fourth parameter, but I would like to leave this function dynamic.
Can someone help me?

[AlB]

Hi @PietroFarias 

Maybe you can use the function Table.ColumnCount( ) to find out the number of columns in the table. Otherwise, can you please provide an example to clarify what you are trying to do?

[Zubair_Muhammad]

@PietroFarias 

If I undertsand the problem correctly,

The fourth argument of Table.Split is optional, so you can eliminate it completely.

Lets say you have column1 to be splitted into multiple columns

= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"})

[PietroFarias]

I did this, but always return only the first column.

[Zubair_Muhammad · Accepted solution]

Hi @PietroFarias 

When i do this, it works

Please see image below and attached file

[Softwaretrain]

Dear Zubair,

Please note that this is a risky solution because not always act correctly.

This method works based on the first data of the column and if your delimiter at the first data is 2 but in another line, you have more than 2 separators it will split just by 2 columns.

Therefore, to be sure that it works correctly you must have the cell which has the most delimiter at the first line.

[MFelix]

Hi @PietroFarias,

Maybe @ImkeF a specialist in M language can help.

Regards,
MFelix