Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 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.
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.