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...
Need Help
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"