Filtering columns

[LEJ]

Hello.

I have a problem filtering some columns.

First of all. The database is created like this (as a range and not as a table. I can not change it). Days and tasks. People have to enter the hours as a value:

There are a lot of columns, but I only need some of them:

I have a list with the name of the columns/tasks that I need:

In the future new tasks will be needed in the report, therefore I will create new columns, but Power BI will not recognize them becase they will not be in the filter.

My question. Is it possible to filter columns keeping in mind a list of names of columns?

Thank you!

[Anonymous · Accepted solution]

Hi @LEJ ,

Add this code in advanced editor.

"Removed Columns" = 
    let _list = Table.ToList(ColumnName)
    in
    Table.RemoveColumns(#"Changed Type",_list)

ColumnName should be the table with all column names.

My Sample:

Then add the above code to advanced editor of second table.

Whole code is as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2A2JzpVgdCmRjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}, {"F", Int64.Type}, {"G", Int64.Type}}),
    #"Removed Columns" = 
    let _list = Table.ToList(ColumnName)
    in
    Table.RemoveColumns(#"Changed Type",_list)
in
    #"Removed Columns"

Result is as below.

You can update ColumnName table and Data table and refresh the report to only load the columns you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mohammedadnant]

Hi @LEJ 

for this reason, instead of selecting the required column,

try removing the unnecessary columns (right-click the columns --> click remove )

example below

Table.RemoveColumns(#"Changed Type",{"Warehouse", "Stock"})

this will help you to add the new column in Power BI automatically if you add it in the excel file in the future.

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https//www.youtube.com/c/taik18