Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Saibal_78
Helper I
Helper I

Create index using duplicate with numbers 1,2,3

Hi All,

I have got a use case where I have some duplicate values in the dataframe with start and end date. Now I want to create an index column as shown below. I want to create the index column considering the start date, latest date being considered as first index.

 

 mmddyyyy 
IDStart DateEnd DateIndex
ABC9/12/20239/30/20231
ABC7/6/20239/11/20232
ABC5/18/20237/5/20233
BDE2/11/20224/11/20221
BDE12/11/20212/10/20222
DRB3/9/20235/17/20231
DRB1/10/20233/8/20232
DRB11/5/20221/9/20233
DRB10/1/202211/4/20224

 

I am able to achieve the purpose using below power query when start dates are not taken into consideration, as soon as I include start date the solution does not work.

 

#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Data", each _, type table [Names=nullable text, Index=number]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Subtable", each Table.AddIndexColumn ([Data], "Index2", 1, 1, Int64.Type )),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Subtable", "Index"}),
#"Expanded Subtable" = Table.ExpandTableColumn(#"Removed Other Columns1", "Subtable", {"ID", "Index2"}, {"ID", "Index2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Subtable",{"Index"}),

#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Index2", "Index"}})

 

Any help would be highly appreciated.

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You were close.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5LDsAgCAXv4toEQa26rLUX6NZ4/2uU+CNpdy/MAK9WdeZLaZUACciQ7dmakZtePMAhGPGLPWBcPIAXnMvNI5orxNntLByXgEM2IpQn88xCWuf5VZD7A+NcsV2NP4yjEnU1/bCB3Y5VN5+3Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Start Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, { {"Index", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Start Date", "End Date", "Index"}, {"Start Date", "End Date", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Start Date", type date}, {"End Date", type date}, {"Index", Int64.Type}})
in
    #"Changed Type1"

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

You were close.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5LDsAgCAXv4toEQa26rLUX6NZ4/2uU+CNpdy/MAK9WdeZLaZUACciQ7dmakZtePMAhGPGLPWBcPIAXnMvNI5orxNntLByXgEM2IpQn88xCWuf5VZD7A+NcsV2NP4yjEnU1/bCB3Y5VN5+3Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Start Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, { {"Index", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Start Date", "End Date", "Index"}, {"Start Date", "End Date", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Start Date", type date}, {"End Date", type date}, {"Index", Int64.Type}})
in
    #"Changed Type1"

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.