# Tags
**Software** : [[02 - Tags/Software/Power Query]]
**Functions** used : [[Table.TransformColumnTypes]] [[Table.Group]] [[Table.RowCount]] [[List.PositionOf]] [[List.Transform]] [[List.FirstN]] [[List.Repeat]] [[Table.FromColumns]] [[Table.ExpandTableColumn]] [[Excel.Workbook]]
**Date** : 17-March-2025
# Description
- Using the power of records in power query i show you how to create a Conditional index on a simple portfolio dataset.
# Video Link
<iframe width="560" height="315" src="https://www.youtube.com/embed/Dboza9G6ogg?si=3PbwcBc-MJyddKJe" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
[GITHUB File Link](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_Counter_Column_Video.xlsx)
# M-Query
```mquery
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PortfolioCode", type text}, {"Date", type date}, {"Cumulative investment Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PortfolioCode"}, {
{"DetailVideo", each _, type table [PortfolioCode=nullable text, Date=nullable date, Cumulative investment Amount=nullable number]},
{"Details", each
[
RowCounter = Table.RowCount(_),
IndexCounter = {1..RowCounter},
CountFromRows = List.PositionOf( List.Transform([Cumulative investment Amount], each _ >= 120), true),
ConditionalIndex = if CountFromRows > 0 then List.FirstN(List.Repeat({0}, CountFromRows) & IndexCounter, RowCounter) else List.Repeat({0}, RowCounter),
ResultTable = Table.FromColumns({[Date], [Cumulative investment Amount], ConditionalIndex}, {"Date","Cumul Amount", "IndexColumn"})
] [ResultTable]
}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Date", "Cumul Amount", "IndexColumn"}, {"Date", "Cumul Amount", "IndexColumn"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Details",{{"Date", type date}, {"Cumul Amount", Int64.Type}, {"IndexColumn", Int64.Type}})
in
#"Changed Type1"
```
## Optional Tags
**Tags** :
**Technique** : [[Records to Decompose Problem]] [[Inner Outer Tables]] [[Nested Let]] [[List Generate Rows]]