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
brief001
Helper II
Helper II

Index Column By Category II

Hi All,

 

I have the table below, with only the two left columns. My wish is to add a column via the Power Query Editor, which should give an index value like in the yellow right column.

Who can help me get this done?

Note it must also be sorted in descending order on the date column.

brief001_0-1621611149368.png

 

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

See this code @brief001 - note my dates are US format = mm/dd/yyyy. It will work with your dates though. You just need to sort however you want first.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Date", Order.Descending}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Sorted Rows",
            {"Name"}, 
            {
                {
                    "All Rows", 
                    each Table.AddIndexColumn(_, "Index", 1, 1),
                    type table [Name=nullable text, Date=nullable date, Index= nullable number]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded All Rows"

 

 

It turns this:

edhans_0-1621612511211.png

into this

edhans_1-1621612528967.png

 

What I did was sort accordingly, then grouped by the first column and created an ALL ROWS aggregation. Then I manually edited the M code to add an index column to that All Rows aggregation, then expanded the Date and Index when done.

 

This was using a simple Excel table I keyed in.

NameDate

Red 1/1/2020
Red 4/1/2020
Red 3/1/2020
Blue 1/4/2020
Blue 5/1/2020

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

negi007
Community Champion
Community Champion

@brief001 Please follow below steps

this is your data

negi007_0-1621613600182.png

 

you can sort the data in the ascending order of color and descanding order of date. To achieve this, first you sort your data by color and then modify the code like below to have two sorts

 

#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending},{"Date", Order.Descending}}),

your data would be like below

negi007_1-1621613697760.png

 

then you need to groupby your table like below

 

negi007_2-1621613737505.png

 

then you add a custom column to your data like below

 

negi007_3-1621613776583.png

 

once you add the index column you can remove all column but new table column. Below is the final output

 

negi007_4-1621613799698.png

 

i am also attaching pbix file for your reference.

 

you may refer to this video as well for help - > https://www.youtube.com/watch?v=a0FqNLI0VsQ

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

6 REPLIES 6
negi007
Community Champion
Community Champion

@brief001 Please follow below steps

this is your data

negi007_0-1621613600182.png

 

you can sort the data in the ascending order of color and descanding order of date. To achieve this, first you sort your data by color and then modify the code like below to have two sorts

 

#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending},{"Date", Order.Descending}}),

your data would be like below

negi007_1-1621613697760.png

 

then you need to groupby your table like below

 

negi007_2-1621613737505.png

 

then you add a custom column to your data like below

 

negi007_3-1621613776583.png

 

once you add the index column you can remove all column but new table column. Below is the final output

 

negi007_4-1621613799698.png

 

i am also attaching pbix file for your reference.

 

you may refer to this video as well for help - > https://www.youtube.com/watch?v=a0FqNLI0VsQ

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

Hi negi007, the first reaction was already worth gold to me. But your solution gave me a little more convenience with your examples. Also because I don't have to use the manual mutation of the M code. And thank you very much for the example file.

negi007
Community Champion
Community Champion

@brief001 I am happy that I was able to help you. Cheers 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

edhans
Super User
Super User

See this code @brief001 - note my dates are US format = mm/dd/yyyy. It will work with your dates though. You just need to sort however you want first.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Date", Order.Descending}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Sorted Rows",
            {"Name"}, 
            {
                {
                    "All Rows", 
                    each Table.AddIndexColumn(_, "Index", 1, 1),
                    type table [Name=nullable text, Date=nullable date, Index= nullable number]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded All Rows"

 

 

It turns this:

edhans_0-1621612511211.png

into this

edhans_1-1621612528967.png

 

What I did was sort accordingly, then grouped by the first column and created an ALL ROWS aggregation. Then I manually edited the M code to add an index column to that All Rows aggregation, then expanded the Date and Index when done.

 

This was using a simple Excel table I keyed in.

NameDate

Red 1/1/2020
Red 4/1/2020
Red 3/1/2020
Blue 1/4/2020
Blue 5/1/2020

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi Edhans, your solution helped me immediately. And I understand your very extensive explanation. Thanks for your quick response!

Glad to help @brief001 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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