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
samueltphilip
New Member

Group by a column and set index

I have a table with a column which has 4 types of values 40HC,40ST,20ST. I aslo have an index column along with other columns. 

Can I group the table in such a way that the index will be 0,1,2,3,4.. for rows with 40 HC, then 0,1,2,3,4...for 40ST and so on. This is the table -- ResultsThis is the table -- Results

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @samueltphilip 

 

First group by "container type" column and perform "All Rows" operation to get a new column. 

vjingzhang_0-1669018100262.png

In the new column, every table value will be like below. 

vjingzhang_3-1669018403356.png

 

Now click the Fx icon next to the formula bar and enter the following code into the formula bar. This will add an Index column to every table value. 

= Table.TransformColumns(#"Grouped Rows", {{"All Rows", each Table.AddIndexColumn(_, "Index", 0, 1)}})

vjingzhang_2-1669018345961.png

 

After that, click the expand icon on the column header of "All Rows" to expand the columns you want. Then reorder columns per your need. 

vjingzhang_4-1669018696669.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUQr3VjAwBNJGBsEhSrE6KOJGOMSNcYib4BAHmW9i4OGMzXxs4sY4xE1wiEPMx+5+bOLGOMRN4OKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Week = _t, #"Container type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Week", type text}, {"Container type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Container type"}, {{"All Rows", each _, type table [Year=nullable number, Week=nullable text, Container type=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"All Rows", each Table.AddIndexColumn(_, "Index", 0, 1)}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(Custom1, "All Rows", {"Year", "Week", "Index"}, {"Year", "Week", "Index"})
in
    #"Expanded All Rows"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @samueltphilip 

 

First group by "container type" column and perform "All Rows" operation to get a new column. 

vjingzhang_0-1669018100262.png

In the new column, every table value will be like below. 

vjingzhang_3-1669018403356.png

 

Now click the Fx icon next to the formula bar and enter the following code into the formula bar. This will add an Index column to every table value. 

= Table.TransformColumns(#"Grouped Rows", {{"All Rows", each Table.AddIndexColumn(_, "Index", 0, 1)}})

vjingzhang_2-1669018345961.png

 

After that, click the expand icon on the column header of "All Rows" to expand the columns you want. Then reorder columns per your need. 

vjingzhang_4-1669018696669.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUQr3VjAwBNJGBsEhSrE6KOJGOMSNcYib4BAHmW9i4OGMzXxs4sY4xE1wiEPMx+5+bOLGOMRN4OKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Week = _t, #"Container type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Week", type text}, {"Container type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Container type"}, {{"All Rows", each _, type table [Year=nullable number, Week=nullable text, Container type=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"All Rows", each Table.AddIndexColumn(_, "Index", 0, 1)}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(Custom1, "All Rows", {"Year", "Week", "Index"}, {"Year", "Week", "Index"})
in
    #"Expanded All Rows"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

This works great.. thanks!

wdx223_Daniel
Super User
Super User

CalculatedColumn=COUNTROWS(FILTER(Table,Table[Container Type]=EARLIER(Table[Container Type])&&Table[Year Week]<EARLIER(Table[Year Week])))

I want the result as Year, Week, True, Forecast, Port, ContainerType,..., Index

                                                                                            20ST              0

                                                                                            20ST               1

                                                                                            20ST                2

etc

                                                                                             40HC              0

                                                                                             40HC              1

                                                                                              40 HC             2

etc

etc

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.

Top Solution Authors
Top Kudoed Authors