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      snummer date Namn  adrsess  Postnummer 934096 null  Smer abc GH784 9...
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"

     

     

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