Forum Discussion

Misha's avatar
Misha
Icon for Helper II rankHelper II
5 years ago
Solved

Format Text in Datamodel

Hi,

 

I 've loaded some data out of two sources and want to link them what's pretty simple...I thought... untill i notoced that my keyvalue has a different format in the  sources...

 

The keyvalue is a  text-format where in the one source it has the format "abcdefghi", in the other source it is formatted as "xabc.def.ghi". De second format is the format used so I have to add a suffix 'x' and the 2 points in between. I can believe this must be rather simple but I can't succeed to find it...

Tx in advance!

 

This is the  - much too heavy - workaround I've created...

 

    #"Ingevoegde eerste tekens" = Table.AddColumn(#"Type gewijzigd", "Eerste tekens", each Text.Start([Ondernemingsnummer], 3), type text),
    #"Ingevoegd tekstbereik" = Table.AddColumn(#"Ingevoegde eerste tekens", "Tekstbereik", each Text.Middle([Ondernemingsnummer], 4, 3), type text),
    #"Ingevoegde laatste tekens" = Table.AddColumn(#"Ingevoegd tekstbereik", "Laatste tekens", each Text.End([Ondernemingsnummer], 3), type text),
    #"Samengevoegde kolom ingevoegd" = Table.AddColumn(#"Ingevoegde laatste tekens", "Samengevoegd", each Text.Combine({[Eerste tekens], [Tekstbereik], [Laatste tekens]}, "."), type text),
    #"Voorvoegsel toegevoegd" = Table.TransformColumns(#"Samengevoegde kolom ingevoegd", {{"Samengevoegd", each "0" & _, type text}})

Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Misha 

    you can do it like this if the length of the string is 9:

    // Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKTklNS8/IVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Split Column by Position" = Table.SplitColumn(Source, "Value", Splitter.SplitTextByRepeatedLengths(3), {"Value.1", "Value.2", "Value.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position",{"Value.1", "Value.2", "Value.3"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),
    #"Added Prefix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each "x" & _, type text}})
in
    #"Added Prefix"

     

3 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Misha 

    you can do it like this if the length of the string is 9:

    // Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKTklNS8/IVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Split Column by Position" = Table.SplitColumn(Source, "Value", Splitter.SplitTextByRepeatedLengths(3), {"Value.1", "Value.2", "Value.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position",{"Value.1", "Value.2", "Value.3"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),
    #"Added Prefix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each "x" & _, type text}})
in
    #"Added Prefix"

     

    • Misha's avatar
      Misha
      Icon for Helper II rankHelper II
      5 years ago

      Tx for your appreciated help FrankAT !! My apologies for my tardive reply. Yours sincerely,

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Misha 

     

    If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

    If not, please kindly elaborate more.