Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
robertosangi
Helper I
Helper I

Index depending on column value

Hi,

 

I have this situation:

 

robertosangi_0-1646996937440.png

I created a conditional column that indexed my value. My problem is that I want this count restart depends on the week value and more i want to index my values depends on "Count" column. 

So i want to create a sort of rank of the week per each value of the "Codice Unità Territoriale" column.

 

How I can do that?

Thank you

1 ACCEPTED SOLUTION

From your table

serpiva64_1-1647246466451.png

you group rows by week and by count (in order to manage situation of parity)

serpiva64_2-1647246582672.png

hten you group by week

serpiva64_3-1647246624411.png

then you order descending your column count inside your groups

serpiva64_4-1647246731096.png

at this point you add and index column

serpiva64_5-1647246782174.png

at this point you first expand Custom

serpiva64_6-1647246870837.png

you obtain this

serpiva64_7-1647246912956.png

Finally you expand AllRows.1 

serpiva64_8-1647246951094.png

remove unnecessary column and that's done

 

 

 

 

 

 

 

 

 

View solution in original post

10 REPLIES 10
serpiva64
Super User
Super User

E' stato un piacere!

serpiva64
Super User
Super User

Hi,

From this:

serpiva64_0-1647002622934.png

you canobtain this

serpiva64_1-1647002651350.png

applying these steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc45DoAwDETRu7hOwUSsZ4ko2EFiuX8H2E4xRYqn2NZPSQYJEr9XSh+SjK5WNbmicXY2qoUW1zzaKTenac+fhfLgs2dmrbzo7u2CzT7OSvW3g9pB7eB2UDuoHdwOage3g9vB7aB2cDusvX8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CoUnTerr = _t, Week = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CoUnTerr", type text}, {"Week", Int64.Type}, {"Count", Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"Week", "Count"}, {{"AllRows", each _, type table [CoUnTerr=nullable text, Week=nullable number, Count=nullable number]}}),
#"Grouped Rows" = Table.Group(#"Grouped Rows1", {"Week"}, {{"AllRows", each _, type table [CoUnTerr=nullable text, Week=nullable number, Count=nullable number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Sort([AllRows],{{"Count", Order.Descending}})),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each Table.AddIndexColumn([Custom.1],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"AllRows", "Index"}, {"AllRows.1", "Index"}),
#"Expanded AllRows.1" = Table.ExpandTableColumn(#"Expanded Custom", "AllRows.1", {"Count"}, {"Count.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded AllRows.1",{"AllRows", "Custom.1"})
in
#"Removed Columns"

 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

 

 

Hi @serpiva64 

Could you please write the steps without code? Is not well understandable but seems work.

 

Thanks

From your table

serpiva64_1-1647246466451.png

you group rows by week and by count (in order to manage situation of parity)

serpiva64_2-1647246582672.png

hten you group by week

serpiva64_3-1647246624411.png

then you order descending your column count inside your groups

serpiva64_4-1647246731096.png

at this point you add and index column

serpiva64_5-1647246782174.png

at this point you first expand Custom

serpiva64_6-1647246870837.png

you obtain this

serpiva64_7-1647246912956.png

Finally you expand AllRows.1 

serpiva64_8-1647246951094.png

remove unnecessary column and that's done

 

 

 

 

 

 

 

 

 

Hi  @serpiva64 I reached this step:

robertosangi_2-1647249332270.png

 

but expanding i obtained:

robertosangi_3-1647249363419.png



I think I missed something but i'm reaching the goal

 

Sorry i forgot to expand one column. You can do it in

serpiva64_0-1647018997071.png

and this is the final result

serpiva64_1-1647019030913.png

 

 

@serpiva64 just to close the question. 

I want to represent this in table. I wanted to insert the data you found in such way:

robertosangi_0-1647252045947.png

 

 

 

How can I do that in a table?
Thanks

Hi,

You can achieve something like this from your previous 

serpiva64_0-1647257644434.png

Add acolumn with W-1

serpiva64_1-1647257714203.png

Merge your query on itself

serpiva64_2-1647257772371.png

Expand

serpiva64_3-1647257794998.png

Change type then add difference column

serpiva64_4-1647257839165.png

change type

then you can create your matrix

 

 

 

 

 

amitchandak
Super User
Super User

Hi @amitchandak 

I watched the video, is similar to my case but doesn't work If I follow the steps she shown. 

Actually is not preciselly my case and for this reason gives me several errors

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.