Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to filter a table in a column before expanding it as the table is extremely large if I don't. I have found another solution to a similar question but it doesn't appear to work for my situation.
My table looks like this
Company | PBI_Change_Log_Entries |
Company 1 | [Table] |
Company 2 | [Table] |
There is a column in the "PBI_Change_Log_Entries" table called "Table_Caption", I'd like to fiter this to rows containing "Vendor Bank Account".
My M query looks like this:
Solved! Go to Solution.
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
Thanks, exactly what I needed