Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm trying to limit the load/Refresh time of the data by adding date filters in the source table. Problem is the filters are dependent of each other. i.e. First Filter is limiting the data set and second filter is further limiting the data set which was left after applying first filter (that's how it's suppose to work I guess).
My question is; Is there a way for me to apply filters on individual column independent of the previous filters?
Thanks.
Solved! Go to Solution.
Hello @Anonymous
then just apply a "or" in your select.rows-function
Here the complete solution
let
Source = #table
(
{"Column A","Column B","Column C","Column D"},
{
{"Name 1","43719","",""}, {"Name 2","","43535","43564"}, {"Name 3","","43558","43565"}, {"Name 4","","42546","43688"}, {"Name 5","43543","42103",""}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"Column B",
each Date.From(Number.From(_)),
type date
},
{
"Column C",
each Date.From(Number.From(_)),
type date
},
{
"Column D",
each Date.From(Number.From(_)),
type date
}
}
),
Filter = Table.SelectRows
(
ToDate,
each [Column B]>#date(2019,1,1) or [Column C]>#date(2019,1,1)
)
in
Filter
Here the core code for doing your filtering
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Anonymous
could you please explain your exact filter criteria?
With the function Table.SelectRows you can really build up big filters with multiple checks.
Jimmy
@Jimmy801 Table.select rows is dependent of the first filter, following is the query which is not working for me:
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns5", each [Column B Date Stamp] > #date(2019, 1, 1)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Column C Date Stamp] > #date(2019, 1, 1))
The filter criteria are different date stamps. For e.g.:
Column A Column B Column C Column D
Name 1 9/11/2019
Name 2 3/11/2019 4/9/2019
Name 3 4/3/2019 4/10/2019
Name 4 6/25/2016 8/11/2019
Name 5 3/19/2019 4/9/2015
I would like to source the data where If I filter Column B by "After 1/1/2019" and it gives me Name 1 and Name 5; and at the same time I'd like to use the filter on Column C "After 1/1/2019" where I get Name 2 and Name 3.
Right now when I'm using the filter on column B it's deleting the data from other columns too
Hello @Anonymous
then just apply a "or" in your select.rows-function
Here the complete solution
let
Source = #table
(
{"Column A","Column B","Column C","Column D"},
{
{"Name 1","43719","",""}, {"Name 2","","43535","43564"}, {"Name 3","","43558","43565"}, {"Name 4","","42546","43688"}, {"Name 5","43543","42103",""}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"Column B",
each Date.From(Number.From(_)),
type date
},
{
"Column C",
each Date.From(Number.From(_)),
type date
},
{
"Column D",
each Date.From(Number.From(_)),
type date
}
}
),
Filter = Table.SelectRows
(
ToDate,
each [Column B]>#date(2019,1,1) or [Column C]>#date(2019,1,1)
)
in
Filter
Here the core code for doing your filtering
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Anonymous ,
We can use "or" in Table.SelectRows function to meet your requirement:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRMjIwtNS31DcEsUEoVgcqaQQRAMsbQ+TBbBN9S4QiYxRFJgg1hgYIRSZwRWb6ZvpGpjBVFiBT4apMkSyzhLBNwZaBHBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t, #"Column D" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type date}, {"Column C", type date}, {"Column D", type date}}),
FilterTable = Table.SelectRows(#"Changed Type", each [Column B] > #date(2019,1,1) or [Column C] >#date(2019,1,1))
in
FilterTable
Best regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.