Dynamically removing columns ending with even numbers

[Mohanraj87]

Hi All,

In Power Query, I have split a column based on "," delimiter and the result produced one column split into 10 columns and columns names have been named as "Column_1", "Column_2", and till "Column_10". Now today based on the values I get 10 columns but tomorrow I may get 20 columns. So, after split, I need to remove all the columns which is ending with even numbers like "Column_2", "Column_4" till the last even numbered columns. Is there a way to do it?

Regards,

Mohanraj

[slorin · Accepted solution]

Hi

With 10 columns

= Table.SplitColumn(#"Changed Type 1", "Details", each List.Alternate(Text.Split(_,","),1,1,1), 10)

 or

= Table.SplitColumn(
      #"Changed Type 1",
      "Details",
      each List.Alternate(Text.Split(_,","),1,1,1), 
      List.Max(
           Table.TransformColumns(
                 #"Changed Type 1",
                 {{"Details", each Number.IntegerDivide(Text.Length(Text.Select(_,",")) + 1, 2) + 1, Int64.Type}}
                 )[Details]
       )
)

https://goodly.co.in/split-by-variable-columns-in-power-query/

Stéphane

[Mohanraj87]

Thanks you very much @slorin your solution worked like a charm.

[slorin]

The principle is not to delete even columns but to select only odd columns with the List.Alternate

= Table.SplitColumn(#"Changed Type 1", "Details", each List.Alternate(Text.Split(_,","),1,1,1))

Stéphane

[Mohanraj87]

Hi @slorin your code worked partially. Meaning the code worked to the lowest length of the column values. 
Eg: For column value 1, raw, 3 I got the result as 1, 3 whereas for the column value 2, name, house, 10, 6, 20 I got the result as 2, house.

[slorin]

Hi

= Table.SplitColumn(Your_Source, "Your_Column", each List.Alternate(Text.Split(_,","),1,1,1))

Stéphane 

[Mohanraj87]

Hi @slorin ,

Below is the screenshot of my Power Query and Advance editor. In the post I have mentioned the column name having "_", but in reality its ".". So, I tried to replace "_" to "." in the advance editor, but it didnt work. 
Let me know as what is wrong.