

February 1, 2019

Hi,
i have 2 tables:
TableName|ColumnName
Table1 | Col1 |
Table1 | Col2 |
Table2 | Col1 |
Table2 | Col2 |
Table2 | Col3 |
From here i am creating Table with column lists for each table:
TableName|List
Table1 | [Table] |
Table2 | [Table] |
And now i have query (which will be the function so i need dynamic variable for tableName) like here:
let
TableName = "Table1",
Source = TableName,
ColumnsToRemove = Table.Column(TableName, "List"){0} //not working --> i need just take the list from TableList to get all columns which i have to keep in Table1 - in this case Col1 and Col2
// table.RemoveColumns(Source,ColumnsToRemove)
in
ColumnsToRemove
So i want to refer to TableList and get List of columns which i have to keep, all other i want to delete.
So in this example Table1 has one additional column which is called "AdditionalOne".
I do not want to have this column in result.
How can i do this?
Best,
Jacek


November 8, 2013

Instead of removing columns, why not provide the list of those you need to keep?
KeepColumns = Table.SelectColumns(TableName, ListOfColumnsToKeep)
or:
KeepColumns = Table.SelectColumns(TableName, List.RemoveItems(Table.ColumnNames(TableName),{"Col1ToRemove","Col2ToRemove"})
(to remove some columns from the existing list of columns)


February 1, 2019

Thank you it is nice.
But how to take the list of columns from TableList to keep ?
How to build funtion in order to make this dynamically?
In other words: how to get column names from TableList based on selected table (filter TableList?), how to create ListOfColumnsToKeep variable dynamically?
Best,
Jacek

Answers Post


February 1, 2019

Ok i added function fnGetColumnsToKeep:
(TableName)=>
let
#"Filter" = Table.SelectRows(TableList, each ([TableName] = TableName))
in
#"Filter"
and tried with:
let
TableName = "Table1",
ColumnsToKeep = Table.Column(fnGetColumnsToKeep(TableName), "List"){0},
LastShape = Table.SelectColumns(TableName, ColumnsToKeep)
in
LastShape
but it throws error:
Why?
Best,
Jacek


February 1, 2019

Hi Catalin,
ok but function fnGetColumnsToKeep is working while invoking function.
Ok so what should i do?
let
TableName = "Table1",
ColumnsToKeep = Table.Column(fnGetColumnsToKeep(TableName), "List"){0},
LastShape = Table.SelectColumns(TableName, ColumnsToKeep)
in
LastShape
How can i get this Table1 as not name but as Table?
So how to from string evaluate Table?


November 8, 2013

TableName="Table1" returns a text.
Source= Excel.CurrentWorkbook(){[Name=TableName]}[Content] returns a table where the table name is provided by the parameter TableName.
Your LastShape step has 2 problems:
LastShape= Table.SelectColumns(TableName, ColumnsToKeep) : Both parameters used are wrong: TableName is not a table, ColumnsToKeep is nowhere in your code, where is this coming from? ColumnsToKeep is supposed to be a list...


February 1, 2019

Hi Catalin,
thank you.
i see the issue.
I have this query with ColumnsToKeep in function but it is creating table which is not the list.
Code:
let
TableName = "Table1",
Source = TableList,
#"Grouped Rows" = Table.Group(Source, {"TableName"}, {{"List", each _, type table [TableName=nullable text, List=table]}}),
#"Filter" = Table.SelectRows(#"Grouped Rows", each ([TableName] = TableName))
in
#"Filter"
i am getting table instead of List. How to create it?
Best,
Jacek


November 8, 2013

As mentioned in your previous topics, a table column is a list.
You have to refer to a single column from a table to get a list of items in that column.
If you just put the column name at the end of a step, the result of that step is a list, no longer a table:
#"Filter" = Table.SelectRows(#"Grouped Rows", each ([TableName] = TableName))[ColumnName]
Make sure in that column you have values, not other objects like tables, the image you attached shows you have tables in the List column, if you refer to that column, you'll get a list of tables, not a list of strings...


February 1, 2019

Thank you very much.
Ok i got it but still struggling with changing table to list type 🙂
let
TableName = "Table1",
Source = TableList,
#"Grouped Rows" = Table.Group(Source, {"TableName"}, {{"ColumnsToKeep", each _, type table [TableName=nullable text, List=table]}}),
#"List" = Table.AddColumn(#"Grouped Rows", "List", each Table.Column([ColumnsToKeep],"ColumnName")),
#"Filter" = Table.SelectRows(#"Added Custom", each ([TableName] = TableName))[Table]
in
#"Filter"
I am getting error while creating #"List" step... Literally the result in table is just "Error" for each row for the column.
Why? What i am doing wrongly?
Please see workbook in attachment.
Best,
Jacek


February 1, 2019

Hmm i was trying to add column and create list from Table.
The syntax is:
Table.Column(table as table, column as text) as list i have no idea what should be there. I tried with: #"List" = Table.AddColumn(#"Grouped Rows", "List", each [ColumnsToKeep]),
but it is creating Table instead of list.
Can you please help?
Jacek


February 1, 2019

I also tried:
let
TableName = "Table1",
Source = TableList,
#"Grouped Rows" = Table.Group(Source, {"TableName"}, {{"ColumnsToKeep", each _, type table [TableName=nullable text, List=table]}}),
#"List" = Table.AddColumn(#"Grouped Rows", "List", each Table.Column(#"Grouped Rows",[ColumnsToKeep])),
#"Filter" = Table.SelectRows(#"Added Custom", each ([TableName] = TableName))[Table]
in
#"Filter"
Best,
Jacek


November 8, 2013

You are chasing your own tail and I have no idea what you're trying to do.
Which is the table you are trying to remove columns from?
#"Grouped Rows" step does not make any sense, because the Source is TableList, which is a grouped table. Why grouping again a grouped table, does not make any sense.


February 1, 2019

ok,
thanks.
I have TableList query like here:
let
Source = Excel.CurrentWorkbook(){[Name="TableList"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TableName", type text}, {"ColumnName", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"TableName"}, {{"Table", each _, type table [TableName=nullable text, ColumnName=nullable text]}}),
#"List" = Table.AddColumn(#"Grouped Rows", "List", each Table.Column([Table],"ColumnName"))
in
List
as you can see i am getting a List of columns for each table here
Next i have fnGetColumnsToKeep function where i am just filtering Table to get proper list:
(TableName)=>
let
Source = TableList,
#"Filter" = Table.SelectRows(Source, each ([TableName] = TableName))
in
#"Filter"
And i finally did it!:
let
Source = Table1,
TableName = "Table1",
ColumnsToKeep = Table.Column(fnGetColumnsToKeep(TableName), "List"){0},
LastShape = Table.SelectColumns(Source, ColumnsToKeep)
in
LastShape
the problem was with the source which i didnt have. I had only STRING "Table1".
Just a question, it is possbile to make the source and use string?
Something like TableSourceName = Evaluate("Table1") and get table from string?
Best,
Jacek
