Flagging the current record in a table

serky

New Member
Joined
Jun 30, 2014
Messages
37
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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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:

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>




 
Last edited:

Forum statistics

Threads
1,084,889
Messages
5,380,446
Members
401,679
Latest member
saffar

Some videos you may like

This Week's Hot Topics

Top