# Flagging the current record in a table

#### serky

##### New Member
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

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),
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

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: