Extract right 6 characters returns incorrect data

[Anonymous]

Hi from ssl

Please see the extract of the data below. The file is avaiale at this link Shared File 

Code

000001
000007
000012
000014
000029
000030
000042
000044
000047
000065
000079
000080
000086

The data is formatted as Text in Excel. All my codes are 6 character length. Some of them are all numerical digits and some with letters. [The attached file contains only a few numeric codes as the problem is only with the numeric codes staring with zeroes.]

In Power BI desktop, when I tried to create a relationship, I was expecting 1-to-many relationship for the Codes column. But it turned out to be many-to-many relationship. My other table is currently with only 15 rows. So I have manually checked twice and confirmed that it has values from the codes column only and no code outside any of the unique codes. So there was no reason why it should return many-to-many relationship.

So I started investigating using Power Query and here is what I found.

Step 1. Checkd for duplicates. No duplicates.

Step 2. Checked for null values. No null values.

Step 3. To be doubly sure, reformatted the column as ABC format in Power Query.

Step 4. Extracted last 6 characters in the same Column. And it returned less than 6 characters for some of the values [e.g. 00007 instead of 000007].

Step 5. Added a column to concanenate 4 leading zeroes.

Step 6. Within the new Column extracted last 6 characters. Still the same result as step no. 4 above.

Step 7. Deleted step 6.

Step 8. Extracted last 6 characters in an added column. Still the same result as in step 4 above.

Step 9. I formatted and reformatted the column again and again.

Step 10. At every outcome in above steps, I tried to establish relationship in Power BI. It always returned many-to-many relationship.

Please help me to identify what the problem is with the source data.

[CNENFRNL]

The dataset consists of some non-printable characters,

Cleanse the dataset before extracting characters,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQAAkOlWJ1oJQODtDQDcwUoGyhqhGCaIISNLBFsYwM408QIIWyCpNwEZqKZKULQHG6GhQFC1MIMyI4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t]),
    #"Selected Numerics" = Table.TransformColumns(Source, {"Code", each Text.Select(_, {"a".."z","A".."Z","0".."9"})}),
    #"6 Chars" = Table.AddColumn(#"Selected Numerics", "Custom", each try Number.ToText(Number.From([Code]),"000000") otherwise [Code])
in
    #"6 Chars"

[Anonymous]

@CNENFRNL

Well this is working, but not useful for me...

I have hundreds of rows of codes and your DAX formula returned only the top 13 rows.

How do I get all the rows?

[Anonymous]

Thank you very much @ CNENFRNL

That helps me understand what is the issue.

I will try this when I get time. Hopefully that works.

[mahoneypat]

When you look at the columns used in the relationship in the data view, highlight each column and confirm that the # of distinct values is the same as the # of rows in the column to be used on the 1 side of the 1:M relationship.  You mentioned that your data has letters in it too.  Note that M is case sensitive but DAX is not, so even if you remove duplicates in your query, you may get there error if you don't first change everything to UPPER or lower case before you remove duplicates.  You can also add a step to remove empty rows (in Remove Rows pull down).

Pat

[Anonymous]

Thanks Pat.

That is good input.

[Fowmy]

@Anonymous 

The file you shared is protected

[Anonymous]

You are right.

The protection was in google drive.

I removed it later.

[Anonymous]

@ Fowmy

I believe it opens in 'protected view'. If you enable contents, you should be able to work on it.

I have not protected it.

Can you please recheck?