Forum Discussion
Custom index colum with multiple conditions
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 🙂
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"
3 Replies
- Ahmedx
Super User
pls try this
Column = VAR t1 ='Table'[SO] RETURN RANKX(FILTER(ALL('Table'),'Table'[SO]=t1),'Table'[Analysis nr],,ASC,Dense) - Ahmedx
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 🙂
