Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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
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
@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.
@qwertzuiop Are you trying to do this in Power Query?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |