Log In
Register
Home
February 1, 2019
Offline
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
See Answer
November 8, 2013
Offline
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
Offline
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
Offline
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
Offline
Anyone?
Best,
Jacek
November 8, 2013
Offline
= Table.SelectColumns(TableName, ColumnsToKeep)
TableName is not a table, it's the NAME of a table. The function needs a table, not a name.
February 1, 2019
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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
November 8, 2013
Offline
= Table.AddColumn(#"Grouped Rows", "List", each Table.Column([ColumnsToKeep],"ColumnName"))
Where is the text in red coming from?
The table from [ColumnsToKeep] does NOT have a column with that name.
February 1, 2019
Offline
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
Offline
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
Offline
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
Offline
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
November 8, 2013
Offline
See post 8 above, I already gave you the answer:
TableName="Table1"
Source= Excel.CurrentWorkbook(){[Name=TableName]}[Content] --> this returns a table based on the table name.
But this is a basic thing, you get this code from ANY query from a table.
