removing row in power bi based on condition

[mohsin-raza]

Hi.

I need help solving this problem.

I have a dataset contain emplyee information . here is a sample 

snummer	date	Namn	adrsess	Postnummer
934096	null	Smer	abc	GH784
934096	2023-06-16 00:00	Smer	abc	GH784

when i tried to remove duplicate rows  by  given Remove duplicate rows option in power query ,it delete row with job-end-date="2023-06-16 00:00" . while i want it will remove row having job-end-date=null. 

I also want  "null" to be remain in the dataset  as it will help for calulation for active employees.

regrads

@mohsin 

[dufoq3]

Hi @mohsin-raza, alternatively you can group rows:

Output

let
    Source = [ a = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQ2MbA0U9JRyivNyVEA0sG5qUVAKjEpGUi6e5hbmCjF6iCpMzIwMtY1MNM1NFMwMLAyMMChJRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [snummer = _t, date = _t, Namn = _t, adrsess = _t, Postnummer = _t]),
    b = Table.TransformColumns(a, {}, each if _ = "null " then null else _)
  ][b],
    ChangedType = Table.TransformColumnTypes(Source,{{"date", type datetime}}),
    GroupedRows = Table.Group(ChangedType, {"snummer", "Namn", "adrsess", "Postnummer"}, {{"T", each Table.FirstN(Table.Sort(_, {{"date", 1}}), 1), type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

[Omid_Motamedise · Accepted solution]

Hi,

You can use this code, or see the attached file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQ2MbA0U9JRAqLg3NQiIJWYlAwk3T3MLUyUYnWQlBgZGBnrGpjpGpopGBhYGRjg0BILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [snummer = _t, date = _t, Namn = _t, #" adrsess" = _t, #" Postnummer" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"snummer", Int64.Type}, {"date", type datetime}, {"Namn", type text}, {" adrsess", type text}, {" Postnummer", type text}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"date", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"snummer", "Namn", " adrsess", " Postnummer"})
in
    #"Removed Duplicates"

[mohsin-raza]

@Omid_Motamedise 

 Thanks alot.Make use of Table.Buffer() help me to solve my problem

regards

[BA_Pete]

Hi @mohsin-raza ,

In Power Query, select the table that you want to remove duplicates for. Press Ctrl+A (select all) to select all columns, then go to Remove Rows > Remove Duplicates. This will remove only rows where ALL column values are duplicated within the row.

Alternatively, you can multi-select (Ctrl+Click) columns to narrow down the columns you want to evaluate duplicates over, as @ZhangKun has alluded to previously.

Pete

[ZhangKun]

Before you use the remove duplicate rows feature, you can select multiple columns(For example, "snummer" and "date").