Forum Discussion

kramaswamy's avatar
kramaswamy
Frequent Visitor
5 years ago
Solved

Conditional Index

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]}}), ...
  • Greg_Deckler's avatar
    5 years ago

    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.