Comparing IDs to identify duplicates (parent/child)

[Anonymous]

Hello,

I have been trying to solve this issue for a while now, on the blink of giving up...

I have the below table which is a ragged hierarchical matrix. I am trying to solve the issue of having duplicates, but got  to a dead end that does not make much sense to me...

The logic consists of setting up the levels, and defining the leaf (when the node is the last) as true. If true, I hide it, else I show it.

It works for some records, while it doesn't for others (no clue why). Highlighted are the abnormally behaving records. Any help would be greatly appreciated!

Thanks,

Anthony

[az38]

Hi @Anonymous 

at first, good practice is to produce trim transform operation in power query editor mode for compared columns Parent and Formatted

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38,

Thank you for the prompt reply.

Both columns don't have any whitespaces. The behavior is a bit weird though. I do not really understand why it is not working as intended...

[az38]

@Anonymous 

so, if you create a simple calculated column for debug, like

Debug Column = IF([Parent PMOID]=[Formatted PMOID], true(), false())

will it return true for all your mistaken rows?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

It is only returning false for all the rows.

I followed a somewhat similar approach created for pivots on Excel. I feel like I am 99% there, but still not there 😞

Here's the link to the followed solution: Parent-child solution 

[az38]

@Anonymous 

how does your data look like in your origin data source?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Link to sample document 

I made sure to create the balanced hierarchy for each level based on the tutorial in the previously provided link

[az38]

Hi @Anonymous 

i wasnt able to repeat your scenario fith dummy data but what ive seen is the "First" isLeaf aggregation.

what if you will try "Last"?

and try to set "Show items with no data" flag for rows

do not hesitate to give a kudo to useful posts and mark solutions as solution