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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Petri
Frequent Visitor

Using a parameter to refer to a column in Power Query

I try to use a parameter to select a (date) column and then filter the latest value in that column. The name of the column varies in source files but is always the first column, so I tried:

 

FirstColumn = Table.ColumnNames(OurTable){0},

 

which in this case returns the correct value (=Date). But the following step produces just the headers of the columns with no data rows:

 

#"Filtered Rows" = Table.SelectRows(OurTable, let latest = List.Max(Table.Column(OurTable,FirstColumn)) in each Table.Column(OurTable,FirstColumn) = latest)

 

 

For trouble shooting, I can see that the following line gives the date 30/09/2022:

 

latest = List.Max(Table.Column(OurTable, FirstColumn)),

 

 

 but what is wrong in the Filtered Rows step above?

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

You can use this syntax in your Filtered Rows step.

 

= let firstcolumn = Table.ColumnNames(Source){0},
latestdate = List.Max(Table.Column(Source, firstcolumn))
in
Table.SelectRows(Source, each Record.Field(_, firstcolumn) = latestdate)

 

Replace Source with your previous step name throughout.

 

Pat

Microsoft Employee

View solution in original post

3 REPLIES 3
ppm1
Solution Sage
Solution Sage

You can use this syntax in your Filtered Rows step.

 

= let firstcolumn = Table.ColumnNames(Source){0},
latestdate = List.Max(Table.Column(Source, firstcolumn))
in
Table.SelectRows(Source, each Record.Field(_, firstcolumn) = latestdate)

 

Replace Source with your previous step name throughout.

 

Pat

Microsoft Employee
wdx223_Daniel
Super User
Super User

guess

NewStep=Table.LastN(Table.Sort(OurTable,Table.ColumnNames(OurTable){0}))

This seems to be on the right track but I got error:  "1 arguments were passed to a function which expects 2". If I modify the line by adding a number in the end:

 

 

NewStep=Table.LastN(Table.Sort(OurTable,Table.ColumnNames(OurTable){0}),3)

 

 

it gives me three rows each of which has the latest (30/09/2022) date. But if I try to replace '3' with something similar to the Last.Max in my original post, I get the same issue. I do not know how may rows there are for each date.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors