Forum Discussion

Hakon's avatar
Hakon
Regular Visitor
2 years ago
Solved

Power query, create new columns based on duplicates and date

Hi,   I am looking to create a column that says if there are any duplicates in the same date, but if there is a duplicate with a newer date then I want to get a value indicating that there is a new...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVMzDUMzIwMlHSUTK0jExLKUoxKXIuqygtS1EwCC7wNfZSitUhRl0WkepKiFNn4kykeVD3mRNSp0KkOkMi1RFrLyRcjAz0DIyIMI+wOqB5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, GUID = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Date", type date}, {"GUID", type text}},"de"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"GUID"}, {{"maxd", each List.Max([Date]), type nullable date}, {"mind", each List.Min([Date]), type nullable date}, {"ct", each Table.RowCount(_), Int64.Type}, {"rows", each _, type table [Date=nullable date, GUID=nullable text, Index=number]}}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"Date", "Index"}, {"Date", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded rows",{"Date", "GUID", "maxd", "mind", "ct", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Duplicates", (k)=> if Table.RowCount(Table.SelectRows(#"Sorted Rows",each [Date]=k[Date] and [GUID]=k[GUID]))>1 then "Duplicate" else ""),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "New/Old", each if [ct] = 1 or [Date]=[maxd] then "New" else "Old"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "GUID", "Duplicates", "New/Old"})
in
    #"Removed Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

lbendlin's avatar
lbendlin
Super User
2 years ago

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hakon's avatar
Hakon
Regular Visitor
2 years ago

This is my source data:

 

DateGUID
15.01.202419Yfdrd4rCvxuvd 0SpM3J
15.01.202419Yfdrd4rCvxuvd 0SpM3j
15.01.202419Yfdrd4rCvxuvd 0SpM3t
15.01.202419Yfdrd4rCvxuvd 0SpM4C
15.01.202419Yfdrd4rCvxuvd 0SpM3J
17.01.202419Yfdrd4rCvxuvd 0SpM3$
17.01.202419Yfdrd4rCvxuvd 0SpM31
17.01.202419Yfdrd4rCvxuvd 0SpM3J
17.01.202419Yfdrd4rCvxuvd 0SpM3j
20.02.202419Yfdrd4rCvxuvd 0SpM3J
20.02.202419Yfdrd4rCvxuvd 0SpM3j

 

This is what I want the outcome to be:

 

DateGUIDDuplicate/UniqueNew/Old
15.01.202419Yfdrd4rCvxuvd 0SpM3JDuplicateOld
15.01.202419Yfdrd4rCvxuvd 0SpM3jUniqueOld
15.01.202419Yfdrd4rCvxuvd 0SpM3tUniqueNew
15.01.202419Yfdrd4rCvxuvd 0SpM4CUniqueNew
15.01.202419Yfdrd4rCvxuvd 0SpM3JDuplicateOld
17.01.202419Yfdrd4rCvxuvd 0SpM3$UniqueNew
17.01.202419Yfdrd4rCvxuvd 0SpM31UniqueNew
17.01.202419Yfdrd4rCvxuvd 0SpM3JUniqueOld
17.01.202419Yfdrd4rCvxuvd 0SpM3jUniqueOld
20.02.202419Yfdrd4rCvxuvd 0SpM3JUniqueNew
20.02.202419Yfdrd4rCvxuvd 0SpM3jUniqueNew

 

I know I can group by and select the colums Date and GUID and see if there is more then 1 of the same GUID in the same date, but I have a problem with saying who GUID is new and old since DAX dont seperate upper and lowercase, and I dont know query so good.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVMzDUMzIwMlHSUTK0jExLKUoxKXIuqygtS1EwCC7wNfZSitUhRl0WkepKiFNn4kykeVD3mRNSp0KkOkMi1RFrLyRcjAz0DIyIMI+wOqB5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, GUID = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Date", type date}, {"GUID", type text}},"de"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"GUID"}, {{"maxd", each List.Max([Date]), type nullable date}, {"mind", each List.Min([Date]), type nullable date}, {"ct", each Table.RowCount(_), Int64.Type}, {"rows", each _, type table [Date=nullable date, GUID=nullable text, Index=number]}}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"Date", "Index"}, {"Date", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded rows",{"Date", "GUID", "maxd", "mind", "ct", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Duplicates", (k)=> if Table.RowCount(Table.SelectRows(#"Sorted Rows",each [Date]=k[Date] and [GUID]=k[GUID]))>1 then "Duplicate" else ""),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "New/Old", each if [ct] = 1 or [Date]=[maxd] then "New" else "Old"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "GUID", "Duplicates", "New/Old"})
in
    #"Removed Other Columns"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

    • Hakon's avatar
      Hakon
      Regular Visitor
      2 years ago

      Thanks, worked perfectly!