Create ID

[jochendecraene]

Hi

I've go a dataset from our telecom operator. It contains incoming and outgoing calls. In the dataset, the calls don't have an unique ID.

I need an id, so how can i create one?

As you can see, one call can be handled in different stages. So I was thinking of following steps to create an unique id:

1) Fill down the 'call time'

2) combine the columns 'call time' & 'caller id'

Is this a proper and safe way or is there a better approach?

thnx, Jochen

[BA_Pete]

Hi @jochendecraene ,

Yes, I think that's a legit process in the absence of better alternatives. I would generally recommend this process over using an index if you need to later relate this table to other tables where the same ID can also be generated in the same way.

Some additional thoughts:

1) This will create a heavy-text field that will take up a lot more space than just the DateTime and Caller ID columns do in aggregate. It *may* also slow down your refreshes for this reason too.

2) Once the ID column is created, you may want to look into grouping your calls so you just have one row per call, with maybe MAX[Ringing] and SUM[Talking] as aggregate columns.

Pete

[jochendecraene]

Thnx for the help!

This seems t work, but I bumt into the problem that de sum gives me an error (SUM[Talking] as aggregate columns)

[BA_Pete · Accepted solution]

Hi @jochendecraene ,

In your formula bar, where the code for the group step shows, change this bit:

{{"Talking_Max", each List.Sum([Talking]), type nullable number}}

To this:

{{"Talking_Max", each List.Sum(List.Transform([Talking], each Number.From(_))), type nullable number}}

You can do the same for the Ringing_Max part of the group function as well.

You will see that I've added a List.Transform function inside the List.Sum that converts the time value to a numerical value before summing.

These are the bits I added:

Once the group is done, you can just change the data type of this column back to Time type and it will give you the correct hours/minutes/seconds.

Pete

[Vijay_A_Verma]

Do like this

Fill down Call Time

Group on Caller ID and Call Time

Then Add an Index

Then expand your table

To see an example - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3MtI3MjAyUjC0MjRU0lEyNDIGko55xUqxOijSRlYmpkAZI2MTJHm4QEBmcnZpAapYaB6SMcYQYywhppiamWNogoiFpBaXKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call Time" = _t, #"Caller ID" = _t, Test1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call Time", type datetime}, {"Caller ID", Int64.Type}, {"Test1", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Call Time"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Caller ID", "Call Time"}, {{"Temp", each _, type table [Call Time=nullable datetime, Caller ID=nullable number, Test1=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Added Index", "Temp", {"Test1"}, {"Test1"})
in
    #"Expanded Temp"