Compare 2 lines in Power Query

Question

Hi guys,I hope you're doing well 🙂I have a dimension table like belowThe hierarchy isCDE_NIV0 &gt; CDE_NIV1 &gt; CDE_NIV2 &gt; CDE_NIV3&gt;CDE_NIV4&gt;CDE_NIV5I 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 levelEx : I have to delete L58 because at L57 there is a same hierarchy with one more level CDE_NIV4&nbsp;The data is not always sorted like this so I have to compare each like with the restI hope that I was clear in my explaination,&nbsp;Here is a sample of the dimension table&nbsp;DATA&nbsp;And here is the result that I would like to get&nbsp;&nbsp;Thank you in advance !

ahmedx · Answer

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

bboy0009 · Answer

yes a moment please

bboy0009 · Answer

Ahmedx&nbsp;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 · Answer

pls try thislet
    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] &lt;&gt;"" and [LIB_NIV4] &lt;&gt; "")),
    #"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) =&gt;  if Table.RowCount(x) &gt; 1 then Table.SelectRows(x, each [CDE_NIV5]&lt;&gt; "") else x}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"CDE_NIV5", "LIB_NIV5"}, {"CDE_NIV5", "LIB_NIV5"})
in
    #"Expanded Count"

Forum Discussion

Related content

Line chart

Add a trend line to a line chart Power BI 2024

Compare to previous line

Table Sort , sort order , comparer

Compare line chart