cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qwertzuiop
Advocate II
Advocate II

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

1 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"

yingyinr_0-1628760026856.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
qwertzuiop
Advocate II
Advocate II

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

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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.

 

qwertzuiop_0-1628602298677.png

 

Do you have any ideas how to handle this?

 

Cheers

qwertzuiop

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"

yingyinr_0-1628760026856.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors