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'm new here, but I have read many posts and have gotten a lot of help from them 🙂 So I wonder if anyone can help med with my problem. Hope it's alright that I post a couple of pictures insted of files.
This is my data: (The dataset has many more colums and about 50000 rows, but the problem I have is with the data below.)
I want an index colum that looks like this:
I can do this in the table view in power BI, but I need to do it in power query. In the table view in power BI, this is what I get: (And this is what I want in power query.)
I've tried to use "group by" in power query, but this is what I get:
I will really appreciate if anybody has the time to help me 🙂 Thanks 🙂
Solved! Go to Solution.
and to know how you can do this in power query, watch my video
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jce5DQAgCADAXahp+GEWwv5raG9iuO66gYgZEFTNYHB9M/flRe7dI5ZXvY/I/D2z6vcqovdzAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SO = _t, #"Analysis nr" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SO", Int64.Type}, {"Analysis nr", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"SO", "Analysis nr"}, {{"Count", each _, type table [SO=nullable number, Analysis nr=nullable number]}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"SO"}, {{"Count", (x)=> Table.AddIndexColumn(x,"Indx",1,1)
}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"SO"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Count", "Indx"}, {"Count.1", "Indx"}),
#"Expanded Count.1" = Table.ExpandTableColumn(#"Expanded Count", "Count.1", {"SO", "Analysis nr"}, {"SO", "Analysis nr"})
in
#"Expanded Count.1"
and to know how you can do this in power query, watch my video
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jce5DQAgCADAXahp+GEWwv5raG9iuO66gYgZEFTNYHB9M/flRe7dI5ZXvY/I/D2z6vcqovdzAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SO = _t, #"Analysis nr" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SO", Int64.Type}, {"Analysis nr", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"SO", "Analysis nr"}, {{"Count", each _, type table [SO=nullable number, Analysis nr=nullable number]}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"SO"}, {{"Count", (x)=> Table.AddIndexColumn(x,"Indx",1,1)
}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"SO"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Count", "Indx"}, {"Count.1", "Indx"}),
#"Expanded Count.1" = Table.ExpandTableColumn(#"Expanded Count", "Count.1", {"SO", "Analysis nr"}, {"SO", "Analysis nr"})
in
#"Expanded Count.1"
Thank you so much, especially for the video! Really impressed that you took the time to make a video! This worked out really well 🙂 Thanks again 🙂
pls try this
Column =
VAR t1 ='Table'[SO]
RETURN
RANKX(FILTER(ALL('Table'),'Table'[SO]=t1),'Table'[Analysis nr],,ASC,Dense)
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 |
---|---|
144 | |
80 | |
65 | |
52 | |
50 |
User | Count |
---|---|
211 | |
87 | |
80 | |
69 | |
60 |