almost identical except for one attribute (filter)

[qwertzuiop]

Hello dear Power BI-Community 🤗

Following problem:

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

Type	Event number	Description	…
Train	A2XSQ	ABC	…
Car	A2XSQ	ABC	…
...	...	...	...

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

[qwertzuiop]

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]

@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]

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

[v-yiruan-msft · Accepted solution]

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

[Greg_Deckler]

@qwertzuiop yeah, but I am on my phone at the moment. Get rid of the countrows in your count variable and rename to __Table. Recreate your __Count variable using Countrows(__Table). Now you can use the IN operator to test if Train is included in the rows.

[Greg_Deckler]

@qwertzuiop Are you trying to do this in Power Query?