Conditional Index

[kramaswamy]

Hi all,

Using the following code to generate an index:

TempAddRowCount = Table.Group(MyTable, {"user_id", "MondayOfWeek"}, {{"Count", each _, type table [user_id=number, start_date=date]}}),
AddRowCount = Table.AddColumn(TempAddRowCount, "Index", each Table.AddIndexColumn([Count],"Index",1))

This works very well for generating my index, however, as it turns out, I need the index to "duplicate" occasionally.

To give a bit more context - I'm trying to take the following data, with the generated Index from the code above:

user_id start_time MondayOfWeek Index
3157064 2021-07-26 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 1
3157064 2021-07-27 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 2
3157064 2021-07-28 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 3
3157064 2021-07-29 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 4
3157064 2021-07-30 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 5
3157064 2021-07-31 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 6
3157064 2021-08-01 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 7

But also have it work for the following data, with the index I want it to generate:

user_id start_time MondayOfWeek Index
3157064 2021-07-26 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 1
3157064 2021-07-27 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 2
3157064 2021-07-28 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 3
3157064 2021-07-29 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 4
3157064 2021-07-30 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 5
3157064 2021-07-30 12:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 5
3157064 2021-07-31 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 6
3157064 2021-07-31 16:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 6
3157064 2021-08-01 8:00:00 AM -07:00 2021-07-26 12:00:00 AM -07:00 7

Currently, what the script does is continues the sequential index, so the above output has numbers 1 through 9.

[Greg_Deckler · Accepted solution]

@kramaswamy How about this? Note, most of this is me trying to get your data clean but it wouldn't copy and paste right. And I have no idea what 16:00:00 AM is as that makes zero sense. But, whatever. The important steps are, duplicate your start_time column but keep all rows, change that duplicated column to Date instead of DateTime, Group By everything except your start_time column, create the Index, expand your group by rows back out. Easy...

let
    Source = Csv.Document(File.Contents("C:\temp\test.csv"),null,{0, 8, 19},ExtraValues.Ignore,1252),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"user_id ", Int64.Type}, {"start_time ", type date}, {"MondayOfWeek Index", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "MondayOfWeek Index", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"MondayOfWeek Index.1", "MondayOfWeek Index.2", "MondayOfWeek Index.3", "MondayOfWeek Index.4", "MondayOfWeek Index.5", "MondayOfWeek Index.6", "MondayOfWeek Index.7", "MondayOfWeek Index.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MondayOfWeek Index.1", type text}, {"MondayOfWeek Index.2", type text}, {"MondayOfWeek Index.3", type duration}, {"MondayOfWeek Index.4", type text}, {"MondayOfWeek Index.5", type text}, {"MondayOfWeek Index.6", type text}, {"MondayOfWeek Index.7", type duration}, {"MondayOfWeek Index.8", Int64.Type}, {"start_time ", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MondayOfWeek Index.7", "MondayOfWeek Index.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","16","9",Replacer.ReplaceText,{"MondayOfWeek Index.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"start_time ", "MondayOfWeek Index.1", "MondayOfWeek Index.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}}),
    #"Merged Columns1" = Table.CombineColumns(#"Changed Type2",{"MondayOfWeek Index.4", "MondayOfWeek Index.5", "MondayOfWeek Index.6"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"Merged.1", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Merged.1", "MondayOfWeek"}, {"Merged", "start_time"}, {"MondayOfWeek Index.8", "Desired"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Desired"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns1", "start_time", "start_time - Copy"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Duplicated Column",{{"start_time - Copy", type date}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type4", {"user_id ", "MondayOfWeek", "start_time - Copy"}, {{"Rows", each _, type table [#"user_id "=nullable number, start_time=nullable datetime, MondayOfWeek=nullable datetime, #"start_time - Copy"=nullable date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows1", "Index", 1, 1, Int64.Type),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index", "Rows", {"start_time"}, {"Rows.start_time"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded Rows",{"start_time - Copy"})
in
    #"Removed Columns3"

@ImkeF @edhans @mahoneypat probably have a better way.

[kramaswamy]

Hi again Greg - thanks for the suggestion. I used your idea and expanded on it slightly. Basically:

GroupRowsByWeekAndDate = Table.Group(MyTable, {"user_id", "MondayOfWeek", "start_date"}, {{"Count", each _, type table [user_id=number, start_date=date, AllData=table]}}),
GroupRowsByWeek = Table.Group(GroupRowsByWeekAndDate, {"user_id", "MondayOfWeek"}, {{"Count2", each _, type table [user_id=number, start_date=date, AllData=table]}}),
AddDailyIndex = Table.AddColumn(GroupRowsByWeek, "Index", each Table.AddIndexColumn([Count2],"Index",1))

Then, when I expanded the table, I kept the index that was assigned to the outer table. That gave me my sequential list I was looking for.

[kramaswamy]

Hey Greg,

I'm not exactly sure I understand your suggestion. From the looks of it, you're grouping by user_id, MondayOfWeek, and the date value of each record - but won't that result in each record having an index of 1, except those which have multiple records per date?