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!