Forum Discussion
Helper IPower Query - Check if a Text is appearing for the 1st time based on Date/Time column
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
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
2 Replies
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
mbuharyThank 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.