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
Anonymous
Not applicable

Index column in power query based on date sorting

Hi! 

I need help creating an index column in Power Query.

I would like to index rows with same ID based on the Date in ascending order. 

 

I would  like it to look something like this: 

 

IDSub IDDateIndex
ABCABC12020-01-011
ABCABC22020-01-022
ABCABC32020-01-033
ABCABC42020-01-044
DEFDEF12020-01-021
DEFDEF22020-01-054
DEFDEF32020-01-043
DEFDEF42020-01-032

 

I have tried to do the following steps: 

1 sort Date acending

2 Group by ID (all rows into "Rank")

3 create a index column with: Table.AddIndexColumn([Rank], "Index", 1, 1))

4 Expand to new rows.

 

The problem is that the first sorting step seems to be ignored. I'm new to this and can't figure out another way to solve it. I'd be so happy and thankful for your help! 

 

Cheers! 

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @Anonymous 

 

this code should do the trick

let
	Source = #table
	(
		{"ID","Sub ID","Date"},
		{
			{"ABC","ABC1","2020-01-01"},	{"ABC","ABC2","2020-01-02"},	{"ABC","ABC3","2020-01-03"},	{"ABC","ABC4","2020-01-04"},	{"DEF","DEF1","2020-01-02"},	{"DEF","DEF2","2020-01-05"},	
			{"DEF","DEF3","2020-01-04"},	{"DEF","DEF4","2020-01-03"}
		}
	),
	ToDate = Table.TransformColumns
	(
		Source,
		{
			{
				"Date",
				each Date.From(_,"en-US"),
				type date
			}
		}
	),
    Group = Table.Group(ToDate, {"ID"}, {{"AllRows", (tableint) => let sort = Table.Sort(tableint,{"Date", Order.Ascending}), AddIndex = Table.AddIndexColumn(sort,"Index",1) in AddIndex}}),
    Expand = Table.ExpandTableColumn(Group, "AllRows", {"Sub ID", "Date", "Index"}, {"Sub ID", "Date", "Index"})
in
	Expand

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

10 REPLIES 10
Jimmy801
Community Champion
Community Champion

Hello @Anonymous 

 

this code should do the trick

let
	Source = #table
	(
		{"ID","Sub ID","Date"},
		{
			{"ABC","ABC1","2020-01-01"},	{"ABC","ABC2","2020-01-02"},	{"ABC","ABC3","2020-01-03"},	{"ABC","ABC4","2020-01-04"},	{"DEF","DEF1","2020-01-02"},	{"DEF","DEF2","2020-01-05"},	
			{"DEF","DEF3","2020-01-04"},	{"DEF","DEF4","2020-01-03"}
		}
	),
	ToDate = Table.TransformColumns
	(
		Source,
		{
			{
				"Date",
				each Date.From(_,"en-US"),
				type date
			}
		}
	),
    Group = Table.Group(ToDate, {"ID"}, {{"AllRows", (tableint) => let sort = Table.Sort(tableint,{"Date", Order.Ascending}), AddIndex = Table.AddIndexColumn(sort,"Index",1) in AddIndex}}),
    Expand = Table.ExpandTableColumn(Group, "AllRows", {"Sub ID", "Date", "Index"}, {"Sub ID", "Date", "Index"})
in
	Expand

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Anonymous
Not applicable

Hi! 

Thank you so much! That worked perfectly!

Me happy! 

 

Stachu
Community Champion
Community Champion

try this code, I sort by ID and Date, the order is preserved then

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtIBkYZAysjAyEDXwBCIlGJ1kOSMkOWMUOWMkeWMUeVMkOVMwHIurm5AQSBpiGkmXA7FPlNUOWM8ZpqguSUWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Sub ID" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Sub ID", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Rank", each _, type table [ID=text, Sub ID=text, Date=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rank], "Index", 0, 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Sub ID", "Date", "Index"}, {"ID", "Sub ID", "Date", "Index"})
in
    #"Expanded Custom"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

Hi! Thank you so much for taking the time to respond! I did however try exactly those steps in your code. The sorting is correct at first. but when I continue to add steps (like merging tables and adding conditional columns) the sorting is lost and index 1 is no longer on the row with the first date. Is there anyway to get around this problem?

Stachu
Community Champion
Community Champion

can you share what steps are you making further? is ti possible to add the index after you make these transformations?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

I add a column from another table with ”New Dates”.

Then I remove duplicates.

Then I add a conditional column saying: if Index = 1 then New Dates, else Date

Stachu
Community Champion
Community Champion

Do you remove the duplicates based on [New Dates] or [Date]? one of them and  the [Index]? the whole table?

I think removing the duplicates messes with the setup, try making it as specific as you can



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi @Anonymous ,

 

Assuming your another table is like that.

33.PNG

 

Then we can merge tables and add a custom column in the merged one.

2.PNG

 

if [Index] = 1 then [Table.new date] else [Date]

Capture.PNG

 

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtIBkYZAysjAyEDXwBCIlGJ1kOSMkOWMUOWMkeWMUeVMkOVMwHIurm5AQSBpiGkmXA7FPlNUOWM8ZpqguSUWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Sub ID" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Sub ID", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Rank", each _, type table [ID=text, Sub ID=text, Date=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rank], "Index", 1, 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Sub ID", "Date", "Index"}, {"ID", "Sub ID", "Date", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Index"}, Table, {"index"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"new date"}, {"Table.new date"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Table", "Custom", each if [Index] = 1 then [Table.new date] else [Date]),
    #"Sorted Rows1" = Table.Sort(#"Added Custom1",{{"ID", Order.Descending}, {"Index", Order.Ascending}})
in
    #"Sorted Rows1"

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi! 

The "new date" table looks like this: 

IDNew Date
ABC2020-01-01
GHJ2020-05-01

 

As soon as i merge another table, the index order gets "flipped" (do not follow the date sorting). 

 

Is there another way to create index column than the one suggested above? 

 

Thanks so much in advance! 

Stachu
Community Champion
Community Champion

can you share the M code for the transformations that you make? You can copy it from Advanced Editor, in the Query Editor Home tab



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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