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) INDEX01.11.2022INC20221 1102.11.2022INC20222 1103.11.2022INC20224 1104.11.2022 0205.11.2022 0206.11.2022INC202271307.11.2022INC202281 308.11.2022INC202291309.11.2022INC202301310.11.2022 04 Thanks in advance!

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

Solution Sage

3 years ago

Solved

Index Column

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!

Need Help

pbix1
3 years ago
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

ronrsnfld

3 years ago

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

7 Replies

pbix1
Resolver II
3 years ago
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
  Resolver II
  3 years ago
  I notice you are a Solution Sage who has provided a lot of solutions to other people. Perhaps I'm missing something?
  - pbix1
    Resolver II
    3 years ago
    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.

mangaus1111

Solution Sage

3 years ago

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

Super User

3 years ago

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

Forum Discussion

Index Column

7 Replies

Related content

Fixed Index Column

dummy index column

Column index

Replace Index Column

Multiple conditions Index