Forum Discussion
Filtering table before expanding column
I would proceed like this:
Table.addcolumn (# "Choose columns", "Company", each Table.SelectRows (_ [PBI_Change_Log_Entries], (r) => r [Table_Caption] = "Vendor Bank Account"))
instead of
# "Renamed columns" = Table.RenameColumns (# "Choose columns", {{"Name", "Company"}}),
then you delete the [name] column and finally expand the [Company] column.
I couldn't test the code, so pay attention to syntax and name matching.
I would proceed like this:
Table.addcolumn (# "Choose columns", "Company", each Table.SelectRows (_ [PBI_Change_Log_Entries], (r) => r [Table_Caption] = "Vendor Bank Account"))
instead of
# "Renamed columns" = Table.RenameColumns (# "Choose columns", {{"Name", "Company"}}),
then you delete the [name] column and finally expand the [Company] column.
I couldn't test the code, so pay attention to syntax and name matching.
Thanks again for this. I have an additional question, how would the step change if the column [table_caption] actually contained dates and I wanted to filter by a date range?
So if for example I wanted to select rows where [table_caption] was between 01/01/2020 and 31/12/2021?
I think this will be useful to know in the near future.
you should change this
Table.addcolumn (# "Choose columns", "Company", each Table.SelectRows (_ [PBI_Change_Log_Entries], (r) => r [Table_Caption] = "Vendor Bank Account"))
to somethink like this
Table.addcolumn (# "Choose columns", "Company", each Table.SelectRows (_ [PBI_Change_Log_Entries], (r) => r [Table_Caption] > #date(2020,1,1) and r [Table_Caption] < #date(2020,12,ù31) ))
but if your choices fall into one of the many cases provided by MS and that you find here you could simplify the filter using one of these functions
Thanks