Removing Columns based on a list of index numbers -Power Query

Question

I have a table, and I have a list of indexes.

Lets say the table is

Column A	Column B	Column C	Column D
Cell 1
Cell 2

And the list is MyList={1,2}

Based on the list, which is the index of the Columns that needs to be removed, I would like to get a new table that consists of Column 0 and 3 which would be

Column A	Column D
Cell 1
Cell 2

Of course in the actual scenario, the table sizes are dynamic, and the list is generated automatically. I need the M code for removing the columns based on the indexes in a list.

I am actually trying to remove the columns in the table where the values are the same. I have gotten so far to retrieving a list of indexes of the columns that need to be removed, and I would appreciate a help in pointing me in the right direction from here.

horseyride · Accepted Answer · 2022-11-17 11:58:45Z

3

To remove columns based on array of column numbers

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
MyList={1,2},
x = Table.RemoveColumns(Source,List.Transform(MyList, each Table.ColumnNames(Source){_}))
in x

To remove columns where contents of rows in that column are identical

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Test", each  if List.Count(List.Distinct([Value]))=1 then true else false}}),
x = Table.RemoveColumns(Source,Table.SelectRows(#"Grouped Rows", each ([Test] = true))[Attribute])
in  x

edited Nov 17, 2022 at 11:58

answered Nov 16, 2022 at 17:06

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

abaci · Accepted Answer · 2022-11-16 17:04:30Z

0

From what I understand from your question, you are trying to remove columns in the table where the values are the same. I hope it was a correct answer.

https://support.microsoft.com/en-us/office/remove-columns-power-query-5c4b5906-84ea-467b-8a80-4edf2c4140cc#:~:text=To%20remove%20a%20single%20column,can%20be%20contiguous%20or%20discontiguous.

answered Nov 16, 2022 at 17:04

abaci

5,2442 gold badges14 silver badges13 bronze badges

Collectives™ on Stack Overflow

Removing Columns based on a list of index numbers -Power Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related