Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
LEJ
Frequent Visitor

Filtering columns

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:

LEJ_0-1653919746020.png

 

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

 

LEJ_1-1653919842109.png

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

LEJ_2-1653919946029.png

 

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!

 

 

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_0-1654155502362.png

RicoZhou_1-1654155523749.png

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.

RicoZhou_2-1654155608939.png

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.

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_0-1654155502362.png

RicoZhou_1-1654155523749.png

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.

RicoZhou_2-1654155608939.png

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
Impactful Individual
Impactful Individual

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.