Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I have this situation:
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
Solved! Go to Solution.
From your table
you group rows by week and by count (in order to manage situation of parity)
hten you group by week
then you order descending your column count inside your groups
at this point you add and index column
at this point you first expand Custom
you obtain this
Finally you expand AllRows.1
remove unnecessary column and that's done
E' stato un piacere!
Hi,
From this:
you canobtain this
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
you group rows by week and by count (in order to manage situation of parity)
hten you group by week
then you order descending your column count inside your groups
at this point you add and index column
at this point you first expand Custom
you obtain this
Finally you expand AllRows.1
remove unnecessary column and that's done
Hi @serpiva64 I reached this step:
but expanding i obtained:
I think I missed something but i'm reaching the goal
Sorry i forgot to expand one column. You can do it in
and this is the final result
@serpiva64 just to close the question.
I want to represent this in table. I wanted to insert the data you found in such way:
How can I do that in a table?
Thanks
Hi,
You can achieve something like this from your previous
Add acolumn with W-1
Merge your query on itself
Expand
Change type then add difference column
change type
then you can create your matrix
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
216 | |
89 | |
76 | |
67 | |
60 |