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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kleszko23
New Member

Filetring table based on the values in last column

How can indicate that it's the last column that should be filtered without hard coding the name of the column. The problem is that name of the last column is different with each report and so it stops working - there is no column name that matches hard coded value.

Here is the code:

let
    Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(Filepath & "\report\file.xlsx"), null, true),
    report_Sheet = Source{[Item="report",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(report_Sheet,1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    KeepLastColumn = 
        Table.SelectColumns(
           #"Promoted Headers",
           { "some1", "some2", "some3", "some4", "some5", "some6", "some7", "some8", List.Last ( Table.ColumnNames ( #"Promoted Headers" ) ) } 
          ),
    #"Filtered Rows" = Table.SelectRows(KeepLastColumn, each ([#"2020-10-31_amount"] = 0))
in
    #"Filtered Rows"

I tried changing it to:

#"Filtered Rows" = Table.SelectRows(KeepLastColumn, each (List.Last ( Table.ColumnNames ( #"Promoted Headers" ) ) = 0))

 but the table comes out empty.

In summary I need to keep columns named "some[1-8]" and the last one. Then, I need to keep only rows that have 0 in the last column (last column name differs with each report).

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@Kleszko23 

another option following up on the approach you had initially (extracting the name):

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGmSrE60UpGQBYyNgCLGgNZyNgcLGoCZCFjC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [some1 = _t, some2 = _t, some3 = _t, some4 = _t, another = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"some1", Int64.Type}, {"some2", Int64.Type}, {"some3", Int64.Type}, {"some4", Int64.Type}, {"another", Int64.Type}}),
    nameLastCol_= List.Last(Table.ColumnNames(#"Changed Type")), 
    filtered_ = Table.SelectRows(#"Changed Type", each Record.Field(_,nameLastCol_)=0)
    
in
    filtered_

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

6 REPLIES 6
AlB
Super User
Super User

@Kleszko23 

and another variation following up on your initial approach, with Expression.Evaluate() to circumvent the issue you were bumping up against:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGmSrE60UpGQBYyNgCLGgNZyNgcLGoCZCFjC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [some1 = _t, some2 = _t, some3 = _t, some4 = _t, another = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"some1", Int64.Type}, {"some2", Int64.Type}, {"some3", Int64.Type}, {"some4", Int64.Type}, {"another", Int64.Type}}),
    nameLastCol_= List.Last(Table.ColumnNames(#"Changed Type")),
    filtered_ = Table.SelectRows(#"Changed Type", each Expression.Evaluate("[" & nameLastCol_ & "]=0",[_=_]))
in
    filtered_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

AlB
Super User
Super User

@Kleszko23 

another option following up on the approach you had initially (extracting the name):

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGmSrE60UpGQBYyNgCLGgNZyNgcLGoCZCFjC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [some1 = _t, some2 = _t, some3 = _t, some4 = _t, another = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"some1", Int64.Type}, {"some2", Int64.Type}, {"some3", Int64.Type}, {"some4", Int64.Type}, {"another", Int64.Type}}),
    nameLastCol_= List.Last(Table.ColumnNames(#"Changed Type")), 
    filtered_ = Table.SelectRows(#"Changed Type", each Record.Field(_,nameLastCol_)=0)
    
in
    filtered_

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Thank you, that is exactly what I needed. Could you please explain the usage of underscore "_" as first parameter in
Record.Field(_,nameLastCol_) or if you could point me to some kind of documentation that would be great too, thanks!

@Kleszko23

the underscore is the input parameter passed to the evaluating function that decides whether to keep the row when filtering. It is a record with the contents of the current row, one field per column, named after the columns. When you write

 Table.SelectRows(Table3, each [Col1] = 3)

  the evaluating function checks whether [Col1] for that record (the current row)  equals 3.  Strictly, it should be _[Col1] = 3  but [Col1] is actually short for _[Col1].

Check this out, it explains it fantastically well:

https://www.excelguru.ca/blog/2018/01/09/each-keyword-power-query/

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

   

mahoneypat
Employee
Employee

Your approach should work.  I tried to replicate it, and at first Table.ColumnNames was showing weird behavior for me too.  It was returning True/False values instead of a list of column names.  I suspect you are seeing same behavior.  I added another step (renamed a column) then deleted it and now Table.ColumnNames is working again.  Can't explain why.

 

I would consider using List.Last(Table.ColumnNames()) to rename the last column and then filter that new column names.  Having the same name in the last columns will be needed if you intend to append the data.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


AlB
Super User
Super User

Hi @Kleszko23 

If it is always the last column, it can be done as follows. Place the following M code in a blank query to see the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGmSrE60UpGQBYyNgCLGgNZyNgcLGoCZCFjC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [some1 = _t, some2 = _t, some3 = _t, some4 = _t, another = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"some1", Int64.Type}, {"some2", Int64.Type}, {"some3", Int64.Type}, {"some4", Int64.Type}, {"another", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Last(Record.ToList(_)) = 0)
in
    #"Filtered Rows"

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors