Forum Discussion
qwertzuiop
Advocate III
Advocate IIIalmost identical except for one attribute (filter)
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 ...
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
qwertzuiopAdvocate III
Advocate IIIThank 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