Format Text in Datamodel

[Misha]

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}})

[v-diye-msft]

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.

[FrankAT · Accepted solution]

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]

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