Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have two different columns 1 is date column and 2. Status column.
I want to select a date from column 1 ( 1-June-2020 to Today)
From the Status column, I want to filter only Validated status how can I do it
Solved! Go to Solution.
Hello @mirfanshahid
you meand to add a filter that filters a daterange and a Status? If so, see this code how to use Table.SelectRows for that
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyUdpbDEnMyUxJLUFKVYHYiEMUTCL79EoQxF0shUz8AImy5jA3y6gJLm2HQZAo2zwCZhhDAOw3WGhljtiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date, "de-DE"}, {"Status", type text}}),
SelectDateAndStatus = Table.SelectRows
(
#"Changed Type",
each [Date]>=#date(2020,06,01) and [Date]<=Date.From(DateTime.FixedLocalNow()) and [Status]="Validated"
)
in
SelectDateAndStatus
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
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 @mirfanshahid
you meand to add a filter that filters a daterange and a Status? If so, see this code how to use Table.SelectRows for that
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyUdpbDEnMyUxJLUFKVYHYiEMUTCL79EoQxF0shUz8AImy5jA3y6gJLm2HQZAo2zwCZhhDAOw3WGhljtiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date, "de-DE"}, {"Status", type text}}),
SelectDateAndStatus = Table.SelectRows
(
#"Changed Type",
each [Date]>=#date(2020,06,01) and [Date]<=Date.From(DateTime.FixedLocalNow()) and [Status]="Validated"
)
in
SelectDateAndStatus
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
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
@mirfanshahid - Sorry, I'm not following, sample data and expected output please.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.