Flagging the current record in a table

[serky]

Hello
I need help converting excel logic to Power Pivot formula. I have a list of supplier names (each have a unique ID, a supplier name and a status). The problem is that a given supplier can actually have multiple records ie - same name but different status record and different ID. Here's an example:

1 FredsFlowers active
2 JohnsStationery inactive
3 FredFlowers inactive
4 SuesBakery active
5 JohnsStationery inactive

I need to flag the 'current' record for a given supplier. In this case it would be ID = 1,2 and 4

The excel logic is:
1. if there is one active record, it's the current record.
2. if there is an active record and multiple inactive records, the active record is the current record.
3. if there is only one record and it's inactive, it's the current record.
4. if there are multiple inactive records, pick the first record in the list to be the current record.

What is the best way to do this? In Power Query (I don't know M) or Power Pivot? If Power Pivot, how do I implement the logic (I tried a calculated column but could only get the formula to work for point 1 above).

Thanks

 

[VBA Geek]

If you start with the the below table named "Data"

You can do it in PQ, yielding the below results:

With the following M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],


    Grp = Table.Group(Source,
              {"Name"},
              {"Tbl", each _, type table},
              GroupKind.Global
             ),


    fnAddFlag = (T as table) as table =>
        let
            ActivePos = List.PositionOf(T[Status], "active", Occurrence.First),
            AddIndexCol = Table.AddIndexColumn(T, "Indx", 0, 1),
            AddFlag   = if 
                            ActivePos > -1
                        then
                            Table.AddColumn(AddIndexCol, "Flag", each if [Indx] = ActivePos then "X" else null, type text)
                        else
                            let 
                                InactivePos = List.PositionOf(T[Status], "inactive", Occurrence.First)
                            in
                                Table.AddColumn(AddIndexCol, "Flag", each if [Indx] = InactivePos then "X" else null, type text)
         in
            Table.RemoveColumns(AddFlag, {"Indx"}),




        TransformCol = Table.SelectColumns(Table.TransformColumns(Grp, {"Tbl", fnAddFlag}), {"Tbl"}),


        ExpandCol = Table.ExpandTableColumn(TransformCol, "Tbl", Table.ColumnNames(TransformCol{0}[Tbl])),


        ChangedType = Table.TransformColumnTypes(ExpandCol,{{"ID", Int64.Type}, {"Name", type text}, {"Status", type text}, {"Flag", type text}}),
        SortedRows = Table.Sort(ChangedType,{{"ID", Order.Ascending}})
in
       SortedRows

If you want to do it via a Calculated Column in Power Pivot then try:

=<br><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#969696"> (</span> Data, [Name] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> [Name] <span class="Parenthesis" style="color:#969696">)</span> && [Status] = <span class="StringLiteral" style="color:#D93124">"Active"</span> <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br>        > <span class="Number" style="color:#EE7F18">0</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#969696"> (</span><br>                Data,<br>                [Name] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> [Name] <span class="Parenthesis" style="color:#969696">)</span><br>                    && [Status] = <span class="StringLiteral" style="color:#D93124">"Active"</span><br>                    && [status] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> [Status] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span><br>            > <span class="Number" style="color:#EE7F18">0</span>,<br><span class="indent8">        </span><span class="StringLiteral" style="color:#D93124">"X"</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">BLANK</span><span class="Parenthesis" style="color:#969696"> (</span><span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#969696"> (</span> Data, [Name] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> [Name] <span class="Parenthesis" style="color:#969696">)</span> && [ID] <= <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> [id] <span class="Parenthesis" style="color:#969696">)</span> <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span><br>            = <span class="Number" style="color:#EE7F18">1</span>,<br><span class="indent8">        </span><span class="StringLiteral" style="color:#D93124">"X"</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">BLANK</span><span class="Parenthesis" style="color:#969696"> (</span><span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>