CODEWITHSUNDEEP
excelpowerquery
Knight
Knight

Reputation: 363

Adding unique index in Excel Power Query

I have a table which looks like 

  Date
  9/4/2016
  9/11/2016
  9/18/2016
  9/25/2016
  10/2/2016
  10/9/2016
  10/16/2016
  10/23/2016
  10/30/2016
  11/6/2016
  11/13/2016
  11/20/2016
  11/20/2016

I'm trying to assign unique index values to 'Date column' but couldn't do it using the 'Add custom index value' in power query which doesn't check duplication. Also I tried "Date.WeekOfYear" which gives number based on year, but I want to assign unique numbers from 1 to .... for dates like 

  Date        Custom_weeknumber
  9/4/2016       1
  9/11/2016      2
  9/18/2016      3
  9/25/2016      4
  10/2/2016      5
  10/9/2016      6
  10/16/2016     7
  10/23/2016     8
  10/30/2016     9
  11/6/2016      10
  11/13/2016     11
  11/20/2016     12
  11/20/2016     12

Any help would be helpful, thanks!

Upvotes: 1

Views: 2435

Answers (2)

Eugene
Eugene

Reputation: 1264

I'd do it this way (which seem to me a bit simplier, I don't like nested tables unless absolutely needed):

  1. Group By Date column
  2. Optional: Sort
  3. Add index
  4. Join source table

Code:

    let
        //Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
        Source = #table(type table [Date = date], {{#date(2016, 10, 12)}, {#date(2016, 10, 13)}, {#date(2016,10,14)}, {#date(2016, 10, 14)}}),
        GroupBy = Table.RemoveColumns(Table.Group(Source, "Date", {"tmp", each null, type any}), {"tmp"}),
        //Optional: sort to ensure values are ordered
        Sort = Table.Sort(GroupBy,{{"Date", Order.Ascending}}),
        Index = Table.AddIndexColumn(Sort, "Custom_weeknumber", 1, 1),
        JoinTables = Table.Join(Source, {"Date"}, Index, {"Date"}, JoinKind.Inner)
    in
        JoinTables

Upvotes: 1

MarcelBeug
MarcelBeug

Reputation: 2967

Assuming:

  1. Your dates are sorted.

  2. The row after duplicates will get the Custom_weeknumber from the duplicates + 1.

Then you can group by dates (with New column name e.g. "DateGroups" and Oparation "All Rows"), add an index column, expand the "DateGroups" field and remove the "DateGroups" field.

Code example created in Power Query in Excel:

let
    Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Grouped = Table.Group(Typed, {"Date"}, {{"DateGroups", each _, type table}}),
    Numbered = Table.AddIndexColumn(Grouped, "Custom_weeknumber", 1, 1),
    Expanded = Table.ExpandTableColumn(Numbered, "DateGroups", {"Date"}, {"DateGroups"}),
    Removed = Table.RemoveColumns(Expanded,{"DateGroups"})
in
    Removed

Upvotes: 2

Related Questions