Forum Discussion

EDANohra's avatar
EDANohra
Regular Visitor
2 years ago
Solved

How to add a logic to filter

Dear Microsoft Community, I'm quite new to the PBI, and facing one dataset that I need to find a way to solve it.   What do I want to achieve? Our service standards say that if a patient is admi...
  • jgeddes's avatar
    2 years ago

    If you are looking for a result of...

    The following code example has the steps using your example data.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MLchJVfDILy7ILEnMUdJRCvYAEgaGur6JlbpGJkC2t4cBFAA5TkGujt5ujsEhQLZjaUm+gntqXmpRYklqilKsDunG+YT6OXtQx6jg0IAA1yDSzDIiw5exAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FacilityName = _t, FacilityID = _t, OrderDate = _t, PatientID = _t, MealPeriod = _t, OrderMode = _t]),
        #"Changed Type" = 
        Table.TransformColumnTypes(
            Source,
            {
                {"FacilityName", type text}, 
                {"FacilityID", type text}, 
                {"OrderDate", type date}, 
                {"PatientID", type text}, 
                {"MealPeriod", type text}, 
                {"OrderMode", type text}
            }
        ),
        Custom1 = 
        Table.Buffer(
            Table.Sort(
                #"Changed Type", 
                {"OrderDate", Order.Ascending}
            )
        ),
        #"Grouped Rows" = 
        Table.Group(
            Custom1, 
            {"FacilityName", "FacilityID", "PatientID"}, 
            {
                {
                    "innerTable", 
                    each Table.SelectColumns(_, {"OrderDate", "MealPeriod"}), 
                    type table [FacilityName=nullable text, FacilityID=nullable text, OrderDate=nullable date, PatientID=nullable text, MealPeriod=nullable text, OrderMode=nullable text]
                }
            }
        ),
        Custom2 = 
        Table.TransformColumns(
            #"Grouped Rows", 
            {
                {
                    "innerTable", 
                    each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)
                }
            }
        ),
        Custom3 = 
        Table.TransformColumns(
            Custom2, 
            {
                {
                    "innerTable", 
                    each Table.AddColumn(_, "Order Mode", each if [Index] > 3 then "Auto Generated" else "Excluded"), 
                    type table [OrderDate=date, MealPeriod=text, Order Mode=text]
                }
            }
        ),
        #"Expanded innerTable" = 
        Table.ExpandTableColumn(
            Custom3, 
            "innerTable", 
            {"OrderDate", "MealPeriod", "Order Mode"}
        )
    in
        #"Expanded innerTable"