Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
Solved! Go to Solution.
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
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
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
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!
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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