Extract right 6 characters returns incorrect data

Question

Hi from ssl&nbsp;Please see the extract of the data below. The file is avaiale at this link&nbsp;Shared File&nbsp;Code000001000007&nbsp;000012000014&nbsp;000029&nbsp;000030000042&nbsp;000044&nbsp;000047&nbsp;000065&nbsp;000079&nbsp;000080&nbsp;000086&nbsp;The data is formatted as Text in Excel.&nbsp;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.]&nbsp;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.&nbsp;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.&nbsp;At every outcome in above steps, I tried to establish relationship in Power BI. It always returned many-to-many relationship.&nbsp;Please help me to identify what the problem is with the source data.&nbsp;

fowmy · Answer

&nbsp;
Anonymous&nbsp;The file you shared is protected

anonymous · Answer

@ Fowmy&nbsp;I believe it opens in 'protected view'. If you enable contents, you should be able to work on it.I have not protected it.&nbsp;Can you please recheck?

mahoneypat · Answer

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.&nbsp; You mentioned that your data has letters in it too.&nbsp; 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.&nbsp; You can also add a step to remove empty rows (in Remove Rows pull down).
&nbsp;
Pat
&nbsp;

cnenfrnl · Answer

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 · Answer

Thanks Pat.That is good input.

Forum Discussion

Extract right 6 characters returns incorrect data

8 Replies

Related content

I need to Extract first 3 character ?

Extract part of string before specific character

Extract Cetrain Characters from Text Column

Extract Special Character String /*

Extract Data from sharepoint

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