Index Column

Hi mangaus1111

I got close with this, but when doing some Googling I actually came across this on this forum. Looks like the same thing. Solved: Create Index that increases every time a value in ... - Microsoft Power BI Community

Hope that helps.

Neil

If I understand you correctly, the solution would be to

• Group by INCLUDE_IN_REPORTS
  • All Rows
  • GroupKind.Local (needs to be added manually
• Add an Index column
• Expand the Grouped Table column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZNLDsIwDETv0nVVxWOnnzViwYYNK1T1bpyFk9GCkDoZr5I4z5H1RlnXrthgNqAAXd/d7pdjZ+/XfnheH/u69TsDZdAyrky0TJyY792vXPPyqC9O/N6kxMzErMTCxCKEFyKsnIj/cKba3LhNpTmYUGXuTIQSwUTNhlNzXrlNzfnIhJpztm+JObaPzBwsKyayOCeorOCcoLKCI0FVgiPBmA2nsoJzgsoKzgkqKzgSL0pwJMdXRTvc8TfRtJ1y2j4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BS_DATE " = _t, TICKET_NUMBER = _t, #" INCLUDE_IN_REPORTS" = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"BS_DATE ", type date}, {"TICKET_NUMBER", type text}, {" INCLUDE_IN_REPORTS", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"TICKET_NUMBER", Text.Trim, type text}, {" INCLUDE_IN_REPORTS", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{" INCLUDE_IN_REPORTS", "INCLUDE_IN_REPORTS"}, {"BS_DATE ", "BS_DATE"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","YES","1",Replacer.ReplaceText,{"INCLUDE_IN_REPORTS"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","0",Replacer.ReplaceValue,{"INCLUDE_IN_REPORTS"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"INCLUDE_IN_REPORTS", Int64.Type}}),

//added steps
    #"Grouped Rows" = Table.Group(#"Changed Type", {"INCLUDE_IN_REPORTS"}, 
    {{"All", each _, type table [BS_DATE=nullable date, TICKET_NUMBER=text, INCLUDE_IN_REPORTS=nullable number]}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"INCLUDE_IN_REPORTS"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"BS_DATE", "TICKET_NUMBER", "INCLUDE_IN_REPORTS"}, {"BS_DATE", "TICKET_NUMBER", "INCLUDE_IN_REPORTS"})
in
    #"Expanded All"

Before Grouping:

After Grouping with Index Column Added

Result