power bi incrementing a sub-index

[se2]

I have the following but can't figure out how to do this with power bi, hopefully with the manual interface.  To the left, you see that index2 is not fully incrementing the way it should as shown to the right?  Any help would be appreciated. Thanks, Steve

[Ashish_Mathur]

Hi,

Do not share a picture.  Share data in a form that can be pasted in an MS Excel workbook.

[se2]

Good suggestion.  How is this done, in this forum?

Steve

[Greg_Deckler · Accepted solution]

@se2 I used this data:

Custom.name1name2Custom.Index2

Bob	Jerry	1
	Jerry	2
	Jerry	3
Tom	Roberta	1
	Roberta	2
	Roberta	3
	Roberta	4
	Roberta	5
	Roberta	6

I only used the data in the first two columns and the full solution is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJR8kotKqpUitWJVsLNCcnPBfKD8pNSi0oSYdJU4cYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name1 = _t, name2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name1", type text}, {"name2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 2, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"name2"}, {{"Table", each _, type table [name1=nullable text, name2=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Table],"Index2",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"name1", "Index2"}, {"Custom.name1", "Custom.Index2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Table"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.name1", "name2", "Custom.Index2"})
in
    #"Reordered Columns"

You can also use the Source line to recreate the data.

[samdthompson]

Hello, 

Here is the step by step fpor what you are are wanting to achieve:

Index within group 

[se2]

when I try the second step, the group by type all records, I get error in each row?

[se2]

[Greg_Deckler]

@se2 Try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJR8kotKqpUitWJVsLNCcnPBfKD8pNSi0oSYdJU4cYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name1 = _t, name2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name1", type text}, {"name2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 2, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"name2"}, {{"Table", each _, type table [name1=nullable text, name2=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Table],"Index2",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"name1", "Index2"}, {"Custom.name1", "Custom.Index2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Table"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.name1", "name2", "Custom.Index2"})
in
    #"Reordered Columns"