Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Extract right 6 characters returns incorrect data

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  ...
Need Help
CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you very much @ CNENFRNL

    That helps me understand what is the issue.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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?