Power Query - Check if a Text is appearing for the 1st time based on Date/Time column

[mbuhary]

Hello,

I have a database with following columns among many other columns:

- Names; and

- Date (with Time)

What I would like to do is to create a custom column where I would like identify if a name is appearing for the 1st time in the database. If so give a value of 1, otherwise make it blank.

We need to accomplish this by checking the "Name" in current record, against all other records whihc has a Date/Time, prior to the current record.

An example of what I am trying to achieve:

Namesss     Date/Time     CusomColumn

Names A     04 Jan 18       Null / 0

Names B     03 Jan 18       1

Names C     02 Jan 18       1

Names A     01 Jan 18       1

Names D     01 Jan 18       Null / 0

Names D     02 Jan 17       Null / 0

Names D     01 Jan 17       1

[v-yuezhe-msft · Accepted solution]

@mbuhary,

Add a blank query in Power BI Desktop, paste the following code into Advanced Editor of the blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTS1WcFTSUTLRN9Q3MjC0UIrVgQk7AYWNMYWdgcJGmMIgQwwxhV1wC0MNMceuGigcCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Names = _t, #"Date/Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}, {"Date/Time", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date/Time", "Date/Time - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date/Time - Copy", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date/Time - Copy", "number"}}),
    Partition = Table.Group(#"Renamed Columns", {"Names"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"number", Order.Ascending}}), "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Names", "Date/Time", "number", "Index"}, {"Partition.Names", "Partition.Date/Time", "Partition.number", "Partition.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each if [Partition.Index]=1 then 1 else null)
in
    #"Added Custom"

Regards,
Lydia

[mbuhary]

Thank you so much.

I was expecting there would be a function in this regard.

Anyways I will try to implement this Partition thing in my database.