How to add Index Column based on date condition

[ashuaswinireddy]

Hello All,

I have a below table

Date	Number
2/3/2023 12:00:00 AM	160
4/14/2023 12:00:00 AM	161
5/16/2023 12:00:00 AM	162
6/23/2023 12:00:00 AM	163
7/23/2023 12:00:00 AM	164
9/26/2023 12:00:00 AM	165
10/20/2023 12:00:00 AM	166
11/3/2023 12:00:00 AM	167

I need to create an index starting from 1 if [Date]>today() otherwise negative index or null values.

Expected Result:

Date	Number	Index	Index
2/3/2023 12:00:00 AM	160	null	-4
4/14/2023 12:00:00 AM	161	null	-3
5/16/2023 12:00:00 AM	162	null	-2
6/23/2023 12:00:00 AM	163	null	-1
7/23/2023 12:00:00 AM	164	null	0
9/26/2023 12:00:00 AM	165	1	1
10/20/2023 12:00:00 AM	166	2	2
11/3/2023 12:00:00 AM	167	3	3

Tried below logic but its creating 53 rows for each date starting 9/26/2023 and adding index from 1 to 53 for each date and repeating that pattern for all dates starting from 9/26/2023.

Index= Table.AddColumn(#"Changed Type", "Custom", each if [Date]>DateTime.LocalNow() then Table.AddIndexColumn(#"Changed Type", "Index", 1, 1,Int64.Type) else null)

Can you please let me know how to achieve the above result. I am able to achieve this using dax but I need this in Query editor not in DAX.

Thank you!

[parry2k · Accepted solution]

@ashuaswinireddy here is the output

[parry2k]

@ashuaswinireddy here is one way, start a blank query -> advanced editor and paste the M code below, change it as you see it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc87CoAwEEXRrYSpA/PJD+0EW91ASKGQ0kZ0/4axTWC6c3kwOcN6PBUs7O911huKzSDoUEicYZmJ2pllawVHUvbIvu+sHpBj30W94WDfqaehe/UJZbAf1Jma9oP4Bzx6MEEpHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}, {"Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Date]>DateTime.LocalNow() then "More" else "Less"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"All", each _, type table [Date=nullable datetime, Number=nullable number, Custom=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each if  [Custom] = "Less" then Table.AddIndexColumn([All],"Index" ,Table.RowCount([All])*-1,1) else Table.AddIndexColumn([All], "Index", 0,1)),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Date", "Number", "Index"}, {"Date", "Number", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom", "All"})
in
    #"Removed Columns"

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️