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.
CodePudding user response：
To remove columns
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
MyList={1,2},
x = Table.RemoveColumns(Source,List.Transform(MyList, each Table.ColumnNames(Source){_}))
in x
Are the contents numbers? You can test for same-ness if the average equals the min and max and remove those columns
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.Average([Value]) = List.Max([Value]) and List.Average([Value]) = List.Min([Value]) then true else false}}),
#"RemoveMe" = Table.SelectRows(#"Grouped Rows", each ([Test] = true))[Attribute],
x = Table.RemoveColumns(Source,#"RemoveMe")
in x
CodePudding user response：
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.