IF statement

[HenryJS]

Hi all,

How can I produce a column that says Live or Terminated with the below parameters:

• Terminated: if any row with the same first part of the Friendly Placement Ref has a row where it is terminated then all terminated
• Live: if any row with the same first part of te Friendly Placement does not have a termination

 

[amitchandak]

You can get a count of termination like

Has Termination = countx(filter(Table,table[PlacementRef] =earlier(table[PlacementRef]) && table[Placement Type] ="Termination"),table[PlacementRef])

The same way other calculations can be done.

[FrankAT]

Hi,

follow these Power Query steps:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjbTNdQ1VNJRCshJTE7NTc0rUYrVAcoYGRpYYpcxMTY2xSpjZGpuboFXxggoE5JalJuZl1iSmZ8HlbOwMMZuk7mZEXY3GJmZm+CXAdnkWlGSmlcMt8fMxNQQuz1IoYDiulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FriendlyPlacementRef = _t, PlacementType = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "FriendlyPlacementRef", "FriendlyPlacementRef - Copy"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"FriendlyPlacementRef - Copy", each Text.BeforeDelimiter(_, "-"), type text}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Text Before Delimiter", "Is Terminated", each if [PlacementType] = "Termination" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"FriendlyPlacementRef - Copy"}, {{"Group", each _, type table [FriendlyPlacementRef=text, PlacementType=text, #"FriendlyPlacementRef - Copy"=text, Is Terminated=number]}}),
    #"Add Status" = Table.AddColumn(#"Grouped Rows", "Add Status", each Table.AddColumn([Group], "New", (r) => if List.Sum([Group][Is Terminated]) > 0 then "Termination" else "Live")),
    #"Removed Other Columns" = Table.SelectColumns(#"Add Status",{"Add Status"}),
    #"Expanded Add Status" = Table.ExpandTableColumn(#"Removed Other Columns", "Add Status", {"FriendlyPlacementRef", "PlacementType", "New"}, {"FriendlyPlacementRef", "PlacementType", "New"})
in
    #"Expanded Add Status"

Figure: Start

Figure: Result

Regards FrankAT