Anonymous

Not applicable

7 years ago

Solved

Power Query - Ordinal row number based on unique id and date/time?

Hi all, I was wondering if it is possible to do one thing in power query: I have a table with interaction ids that can appear in multiple rows with different time stamp. Repetition happens when interaction is opened again and again. I would like to create custom column with ordinal number for each interaction in a data table. This would look something similar to below:

Interaction ID	Date/time	Ordinal Number
123	4/4/2019 13:00	1
345	4/5/2019 15:00	1
567	4/6/2019 19:00	1
123	4/7/2019 18:00	2
123	4/8/2019 13:30	3
567	4/9/2019 16:15	2
345	4/10/2019 12:00	2
123	4/11/2019 11:00	4
345	4/12/2019 13:00	3

Is this possible outside of DAX

Regards

Filarap

Need Help

parry2k

7 years ago

Anonymous here is sample power query, change it as per your need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc/LCUAhDETRVh5ZC2YS468Vsf82BIkPdH3gTjIGQZQCpZiiMNoH7cw0wyBNtsEc7IDlsiE7tAMnVRzqC/Xf0DvVHHKHXeNgF3lbgAvegyHXK3MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Interaction ID" = _t, Date = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Interaction ID"}, {{"Count", each Table.RowCount(_), type number}, {"AllRows", each _, type table [Interaction ID=text, Date=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Count"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"Date"}, {"Date"})
in
    #"Expanded AllRows"

parry2k

7 years ago

Anonymous try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc/LCUAhDETRVh5ZC2YS468Vsf82BIkPdH3gTjIGQZQCpZiiMNoH7cw0wyBNtsEc7IDlsiE7tAMnVRzqC/Xf0DvVHHKHXeNgF3lbgAvegyHXK3MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Interaction ID" = _t, Date = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Interaction ID"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}),
    #"Expanded Rank1" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Date", "Rank"}, {"Date", "Rank"})
in
    #"Expanded Rank1"

v-danhe-msft

7 years ago

Hi Anonymous ,

You could refer to below code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc5BCoAgEIXhq8SsAx11LF276QziQsJFm4Lw/hQyBdny8fHDixGWvZYzr3U79mEJMELItUAaI6DS9zTCCCXRDai9lB+YGOYe5rfQX0BkwSfRhpoQA/WAkkX9RPXHyE5NLIPrwTFYjwQpXQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Interaction ID", Int64.Type}, {"Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Interaction ID"}, {{"A", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded A" = Table.ExpandTableColumn(#"Grouped Rows", "A", {"Date", "Index"}, {"A.Date", "A.Index"})
in
    #"Expanded A"

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

Index.pbix29 KB

Ashish_Mathur

7 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Interaction ID", Int64.Type}, {"Date", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Interaction ID", Order.Ascending}, {"Date", Order.Ascending}}),
    Partition = Table.Group(#"Sorted Rows", {"Interaction ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Partition"

Hope this helps.

7 Replies

parry2k

Super User

7 years ago

Anonymous here is sample power query, change it as per your need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc/LCUAhDETRVh5ZC2YS468Vsf82BIkPdH3gTjIGQZQCpZiiMNoH7cw0wyBNtsEc7IDlsiE7tAMnVRzqC/Xf0DvVHHKHXeNgF3lbgAvegyHXK3MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Interaction ID" = _t, Date = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Interaction ID"}, {{"Count", each Table.RowCount(_), type number}, {"AllRows", each _, type table [Interaction ID=text, Date=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Count"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"Date"}, {"Date"})
in
    #"Expanded AllRows"

Anonymous
Not applicable
7 years ago
Thank you parry2K,

With this query i was able to get
Interaction IDDateIndex
123 4/4/2019 13:00 1
123 4/7/2019 18:00 1
123 4/8/2019 13:30 1
123 4/11/2019 11:00 1
345 4/5/2019 15:00 2
345 4/10/2019 12:00 2
345 4/12/2019 13:00 2
567 4/6/2019 19:00 3
567 4/9/2019 16:15 3

But i am trying to achieve ordinal numbers based on ID sorted by date/time (Earliest would be number 1)

Interaction ID Date Index
123 4/4/2019 13:00 1
123 4/7/2019 18:00 2
123 4/8/2019 13:30 3
123 4/11/2019 11:00 4
345 4/5/2019 15:00 1
345 4/10/2019 12:00 2
345 4/12/2019 13:00 3
567 4/6/2019 19:00 1
567 4/9/2019 16:15 2

Is this possible?
Filarap
- parry2k
  Super User
  7 years ago
  Anonymous try this
  
  let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc/LCUAhDETRVh5ZC2YS468Vsf82BIkPdH3gTjIGQZQCpZiiMNoH7cw0wyBNtsEc7IDlsiE7tAMnVRzqC/Xf0DvVHHKHXeNgF3lbgAvegyHXK3MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Interaction ID" = _t, Date = _t]), #"Grouped Rows" = Table.Group(Source, {"Interaction ID"}, {{"Rank", each Table.AddIndexColumn(_, "Rank", 1)}}), #"Expanded Rank1" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Date", "Rank"}, {"Date", "Rank"}) in #"Expanded Rank1"

v-danhe-msft

Microsoft Employee

7 years ago

Hi Anonymous ,

You could refer to below code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc5BCoAgEIXhq8SsAx11LF276QziQsJFm4Lw/hQyBdny8fHDixGWvZYzr3U79mEJMELItUAaI6DS9zTCCCXRDai9lB+YGOYe5rfQX0BkwSfRhpoQA/WAkkX9RPXHyE5NLIPrwTFYjwQpXQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Interaction ID", Int64.Type}, {"Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Interaction ID"}, {{"A", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded A" = Table.ExpandTableColumn(#"Grouped Rows", "A", {"Date", "Index"}, {"A.Date", "A.Index"})
in
    #"Expanded A"

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

Index.pbix29 KB

Forum Discussion

Power Query - Ordinal row number based on unique id and date/time?

7 Replies

Related content

Date/Time

date/time addition

Extract date from date and time

Date TIme Change

date time format

Interaction ID	Date	Index
123	4/4/2019 13:00	1
123	4/7/2019 18:00	2
123	4/8/2019 13:30	3
123	4/11/2019 11:00	4
345	4/5/2019 15:00	1
345	4/10/2019 12:00	2
345	4/12/2019 13:00	3
567	4/6/2019 19:00	1
567	4/9/2019 16:15	2