Index Column

[mangaus1111]

Hi Community,

my desired result is to obtain a final table with 4 columns. How can I modifiy the code in my pbi file?

https://1drv.ms/u/s!Aj45jbu0mDVJi1tBwZJ2MKNo7NPi?e=AIWKwU

1) BS_DATE    2) TICKET_NUMBER   3) INCLUDE_IN_REPORTS    4) INDEX

01.11.2022	INC20221	1	1
02.11.2022	INC20222	1	1
03.11.2022	INC20224	1	1
04.11.2022		0	2
05.11.2022		0	2
06.11.2022	INC20227	1	3
07.11.2022	INC20228	1	3
08.11.2022	INC20229	1	3
09.11.2022	INC20230	1	3
10.11.2022		0	4

Thanks in advance!

[pbix1 · Accepted solution]

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

[mangaus1111]

Hi @pbix1 ,

I attach a link with a new pbi file. Now the source does not refer to an Excel file and I hope you can see all the script in the advanced editor.

https://1drv.ms/u/s!Aj45jbu0mDVJi1tBwZJ2MKNo7NPi?e=zgvklo

The big problem is not to create the column [INCLUDE_IN_REPORTS], but the column [INDEX].

The column [INDEX] must be increased of 1 anytime the column [INCLUDE_IN_REPORTS] switch from 0 to 1 or from 1 to 0.

[ronrsnfld · Accepted solution]

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

[pbix1]

Hi mangaus1111

Couple of issues here. The source refers to an Excel file that's local to you, so I can't modify the code. However, by the look of it, you would just need to do an 'if is null' condition in a conditional column to get the 'include in report flag'. You can do this using the GUI. null needs to be entered as 'null'. Regarding the index, it's not apparent from your sample what you want the index based on. Again there is an add index column in the GUI, but if it needs to be more complex than what this offers, you might need a custom column with some custom code. If you can provide some more detail on what you're after, and change the source to a manual table, (you can just copy/paste from Excel), then myself or someone else might be able to help you further.

Neil

[pbix1]

I notice you are a Solution Sage who has provided a lot of solutions to other people. Perhaps I'm missing something?

[pbix1]

OK, so I was missing something. I see now how you want to get the index. Not a quick one for me I'm afraid. Hopefully someone else can help.

[pbix1]

Maybe incorporate some kind of running sum and combine it with an index.

[pbix1 · Accepted solution]

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