cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mangaus1111
Solution Sage
Solution Sage

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.2022INC20221 11
02.11.2022INC20222 11
03.11.2022INC20224 11
04.11.2022  02
05.11.2022  02
06.11.2022INC2022713
07.11.2022INC202283
08.11.2022INC2022913
09.11.2022INC2023013
10.11.2022  04

 

Thanks in advance!

2 ACCEPTED SOLUTIONS

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

View solution in original post

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:

ronrsnfld_0-1669064889129.png

After Grouping with Index Column Added

ronrsnfld_1-1669064924267.png

Result

ronrsnfld_2-1669064952599.png

 

 

 

 

 

View solution in original post

7 REPLIES 7
mangaus1111
Solution Sage
Solution Sage

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.

 

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:

ronrsnfld_0-1669064889129.png

After Grouping with Index Column Added

ronrsnfld_1-1669064924267.png

Result

ronrsnfld_2-1669064952599.png

 

 

 

 

 

pbix1
Resolver I
Resolver I

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

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

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.

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

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors