Forum Discussion

mohsin-raza's avatar
mohsin-raza
Icon for Helper III rankHelper III
1 year ago
Solved

removing row in power bi based on condition

Hi.

I need help solving this problem.

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


 

 

snummerdateNamn adrsess Postnummer
934096null SmerabcGH784
9340962023-06-16 00:00SmerabcGH784


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 

 

Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

    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"

     

     

5 Replies

  • ZhangKun's avatar
    ZhangKun
    Icon for Super User rankSuper User
    1 year ago

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

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    1 year ago

    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

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    1 year ago

    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"

     

     

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    1 year ago

    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