how do we get the row number at the visual level

[Raj007]

Hi All, thanks for your time, 

I am trying to create the table visual where i need a field something like row number so i have used create index starting from 1

but when i pull the  index column into the table (used do not summarize) but the numbers shown are not in the sequence like 1 to 28 for example  - screenshot below - i have duplicates in my ID field - any help please?

[Vera_33]

Hi @Raj007 

When you said create index starting from 1, do you mean add an index column in Power Query?

When you said  i have duplicates in my ID field, what does it mean? No ID field in the screenshot....

Or you mean you need to index per ID, so for each ID, index from 1...n? You can groupby ID then add Index. If my understanding is not correct, please provide some sample data and expected result in a format people can copy.

[Raj007]

Hi Vera,

thank you so much,

I dont know the reason but i do not see the attach file something like attachment to share ...

Below is the sample data.. i have duplicate IDs because of the production app has different values

I need to show the row number in the table visual  with the columns like 

Row Number, Count(AppID),App Owner

when I use the Index in query editor i dont get the numbers in the sequence order - any help pls

AppID AppName AppOwner GRI_SUM Production Process Name
101 Digital Communications Dave 1 Digital103
101 Digital Communications Dave 1 Digital102
101 Digital Communications Dave 0 Digital101
102 Escreening Scott 1 Escreen102
102 Escreening Scott 1 Escreen102_V1
102 Escreening Scott 1 Escreen102_V2
102 Escreening Scott 0 Escreen102_v3
103 HealthCheck James 1 HC1
103 HealthCheck James 0 HC2
103 HealthCheck James 1 HC3
103 HealthCheck James 0 HC4
103 HealthCheck James 1 HC5
103 HealthCheck James 0 HC6
103 HealthCheck James 1 HC7
103 HealthCheck James 0 HC8
103 HealthCheck James 1 HC9
103 HealthCheck James 0 HC10
104 CreditVerification Dana 1 CV1
104 CreditVerification Dana 1 CV2
104 CreditVerification Dana 1 CV3
104 CreditVerification Dana 1 CV4
104 CreditVerification Dana 1 CV5
104 CreditVerification Dana 1 CV6
104 CreditVerification Dana 1 CV7
104 CreditVerification Dana 1 CV8
104 CreditVerification Dana 0 CV9
104 CreditVerification Dana 0 CV10
104 CreditVerification Dana 0 CV11
104 CreditVerification Dana 0 CV12
104 CreditVerification Dana 0 CV13
104 CreditVerification Dana 0 CV14
104 CreditVerification Dana 0 CV15
104 CreditVerification Dana 0 CV16
104 CreditVerification Dana 0 CV17
104 CreditVerification Dana 0 CV18
104 CreditVerification Dana 0 CV19

[Vera_33]

Hi @Raj007 

I still don't get your expected results, like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndLNDoIwEATgVzGcObSACudqYryacDHGNFixUUoC1ee34vp3YtYTKXw7mzDdbiMpZBRHC1tbry8T1TbN1dlKe9u6/vFB30x4fBkp0mgX/zWYMAbF96CkwSS8XPZVZ4yzrg6HTdV6T2vow2cNqPclM35fjm4QvwO31y9Lw/uV0Rd/UidTncNprRvT04qVkqNODC4B88b3PvMyMG8K5s3AvDmYl4N5BZgnBcEsHFVnDtaXprNHuofDNXSaYtX7gmA6YemUpTOWnrL0jKXnLJ2DWgy6YGm4TOJom8TROomjfRJHCyWONkocrZQ42ilxXqkytLq7Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AppID = _t, AppName = _t, AppOwner = _t, GRI_SUM = _t, #"Production Process Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AppID", Int64.Type}, {"AppName", type text}, {"AppOwner", type text}, {"GRI_SUM", Int64.Type}, {"Production Process Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"AppID"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrows],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"AppID", "AppName", "AppOwner", "GRI_SUM", "Production Process Name", "Index"})
in
    #"Expanded Custom"

[Raj007]

Hi Vera, i am trying to show the row number in the table visual...

what i did was created an index under add new column (index starting from 1) -

when i use the index column in the table visual its not showing the row number in sequence order like 1,2,3,4,5....10  this order - rather its showing a different order

[Vera_33]

sorry, I still don't get it...so you mean the index is correct, just the order is not? you can sort it...but I don't see the connection from your first screenshot with your sample data...

[Raj007]

Hi Vera, first screenshot ignore it... as i could not attach the file i came up with something else 🙂 sorry about the confusion

yes, i want to show the row number as sequential number but when i use the index i dont see the sequential number instead i see it like 1,5,7, etc  -  do you want me to put the same data that is in screenshot 1 for testing

[Raj007]

ID Claim ID Claim Date Delivery Date Product Claimed Delivery Company Claim Cost City State
1 2446 3/31/2010 8/23/2017 TV FedEx 220 Knoxville Tennessee
2 4190 5/20/2016 6/13/2019 TV UPS 450 Phoenix Arizona
3 1057 8/7/2012 1/31/2013 Play Station DHL 550 Knoxville Tennessee
4 3953 10/17/2011 5/13/2013 Printer FedEx 220 Greenville South Carolina
5 3347 11/8/2016 11/8/2016 Printer FedEx 220 Nashville Tennessee
6 2645 2/12/2014 7/20/2018 Microwave FedEx 150 Nashville Tennessee
7 3819 4/16/2010 2/16/2018 TV DHL 450 Greenville South Carolina
8 1268 10/15/2011 4/22/2013 Play Station FedEx 550 Sacramento California
9 2495 8/10/2016 12/17/2019 TV USPS 450 Nashville Tennessee
10 1354 2/11/2011 9/2/2016 TV FedEx 450 Greenville South Carolina
11 2710 1/29/2016 2/11/2019 Play Station DHL 550 Knoxville Tennessee
12 2704 10/12/2010 2/2/2017 TV FedEx 450 Knoxville Tennessee

[Raj007]

I am not able to upload the file..

[Vera_33]

Hi @Raj007 

I don't know how to upload files, either...but you always gave different data, so very much confusing...so did you mean you have the ID in the entries, so when you drag Index column to the visual, they are agregated instead of 1,2,3? say you have one ID has two Index 1, 2, it becomes 3? then don' summarize the column

If it is not the case, send me a .pbix file. I will pm you my email.

[Raj007]

Hi Vera, thank you so much, i am sorry, i got pulled away for some days.. do you think i can send you the .pbix file , pls let me know the email id

[Vera_33]

Hi @Raj007 

I sent you my email in pm, haven't you received it?