Forum Discussion

qwertzuiop's avatar
qwertzuiop
Icon for Advocate III rankAdvocate III
5 years ago
Solved

almost identical except for one attribute (filter)

Hello dear Power BI-Community ðŸ¤—

 

Following problem:

 

Let's assume the following table with thousands of rows:

 

TypeEvent numberDescription…

Train

A2XSQABC…
CarA2XSQABC…
............

 

In the table there can be rows that are almost identical except for the attribute "Type".

In this case, if e.g. the event number occurs twice, the goal is to keep only the line with the type "Train".

 

Any ideas how to solve it?

 

Thank you very much for your contribution.

 

Cheers

qwertzuiop

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi qwertzuiop ,

    You can apply the following codes in Advanced Editor to achieve it:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzMxT0lFyNIoIDgTRTs5KsTrRSs6JRVhEYaqdnNxcQMJRLijCrm4u3uEgcZ9gTyRTgr3DPYGUZ7h3MC7RWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, #"Event number" = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Event number", type text}, {"Description", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Event number"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Type", "Description", "Index"}, {"Type", "Description", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Index", each ([Index] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

    Best Regards

6 Replies

  • qwertzuiop's avatar
    qwertzuiop
    Icon for Advocate III rankAdvocate III
    5 years ago

    Hi Greg_Deckler 
    Personally I don't mind where the problem is solved (In Power-Query Editor or as a calculated Table oder Measure or something)

     

    Thanks for your quick support 🙂

     

    Cheers

    qwertzuiop

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      qwertzuiop Well, one way that you could do this is to create the following column:

      Keep Column =
  VAR __EventNumber = [Event number]
  VAR __Description = [Description]
  VAR __Count = COUNTROWS(FILTER('Table',[Event number] = __Eventnumber && [Description] = __Description))
RETURN
  SWITCH(TRUE(),
    __Count = 1,1,
    __Count > 1 && [Type] = "Train",1,
    0
  )

      You can then use this column in your Filters pane or in DAX measures.

       

      • qwertzuiop's avatar
        qwertzuiop
        Icon for Advocate III rankAdvocate III
        5 years ago

        Thank you very much Greg_Deckler 

         

        Code works as expected, but when running it I discovered a situation that is not taken into account in the code.

         

        For all dublicate event numbers where Type in both is "Car", the code contains a 0 (see piture)

        At least one of them should be a 1.

         

         

        Do you have any ideas how to handle this?

         

        Cheers

        qwertzuiop