Forum Discussion

bboy0009's avatar
bboy0009
Helper II
3 years ago

Compare 2 lines in Power Query

Hi guys,

I hope you're doing well 🙂

I have a dimension table like below

The hierarchy is

CDE_NIV0 > CDE_NIV1 > CDE_NIV2 > CDE_NIV3>CDE_NIV4>CDE_NIV5

I would like to remove all lines where there is a red cross. The logic is delete all lines if at others lines we have same hierarchy with one more level

Ex : I have to delete L58 because at L57 there is a same hierarchy with one more level CDE_NIV4

 

The data is not always sorted like this so I have to compare each like with the rest

I hope that I was clear in my explaination,

 

Here is a sample of the dimension table 

DATA 

And here is the result that I would like to get

 

 

Thank you in advance !

Need Help

4 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    Share some data to work with (in a format the can be pasted in an MS Excel file) and show the expected result.

    • bboy0009's avatar
      bboy0009
      Helper II
      3 years ago

      Ahmedx 

      I just edited the post. You can find a link to download a sample of data and an image of the result that I would like to get

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    pls try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVDBCoMwDP2V4lmhOhxeyxR3GFI2ZQcnUqTbCmql1f9fq3NT7yqE5L30kbwmTQ0/DLBhjgXhk86SEYBJwZ6s0DTRqZOtICUjfeMWX1WNeUtKEDw6CJ0jBlPJHVmO51p4DlHSQ6j0Xj9TcEHAwQeCNoIXVEpWv4zM3M2WDV3Fz7zkNa2IZW/vZYR5hBZscys6VlwaXqIwTxrVoKT6crDgv/fhHDMJ4O2bij0drnwglOTDF2dw9aVKMlx7Alde+o8s+wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CDE_NIV0 = _t, LIB_NIV0 = _t, CDE_NIV1 = _t, LIB_NIV1 = _t, CDE_NIV2 = _t, LIB_NIV2 = _t, CDE_NIV3 = _t, LIB_NIV3 = _t, CDE_NIV4 = _t, LIB_NIV4 = _t, CDE_NIV5 = _t, LIB_NIV5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CDE_NIV0", type text}, {"LIB_NIV0", type text}, {"CDE_NIV1", type text}, {"LIB_NIV1", type text}, {"CDE_NIV2", type text}, {"LIB_NIV2", type text}, {"CDE_NIV3", type text}, {"LIB_NIV3", type text}, {"CDE_NIV4", type text}, {"LIB_NIV4", type text}, {"CDE_NIV5", type text}, {"LIB_NIV5", type text}}),
    Custom1 = Table.SelectRows( #"Changed Type" ,each ([CDE_NIV4] <>"" and [LIB_NIV4] <> "")),
    #"Grouped Rows" = Table.Group(Custom1, {"CDE_NIV0", "LIB_NIV0", "CDE_NIV1", "LIB_NIV1", "CDE_NIV2", "LIB_NIV2", "CDE_NIV3", "LIB_NIV3", "CDE_NIV4", "LIB_NIV4"}, {{"Count", (x) =>  if Table.RowCount(x) > 1 then Table.SelectRows(x, each [CDE_NIV5]<> "") else x}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"CDE_NIV5", "LIB_NIV5"}, {"CDE_NIV5", "LIB_NIV5"})
in
    #"Expanded Count"