Forum Discussion
EDANohra
2 years agoRegular Visitor
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...
- 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"
EDANohra
2 years agoRegular Visitor
Hello Bhanu,
It unfortunately didn't work out for my dataset.
Appreciate your effort.