Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Misha
Helper II
Helper II

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

1 ACCEPTED SOLUTION
FrankAT
Community Champion
Community Champion

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"

 

21-09-_2020_15-16-34.png

View solution in original post

3 REPLIES 3
v-diye-msft
Community Support
Community Support

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
FrankAT
Community Champion
Community Champion

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"

 

21-09-_2020_15-16-34.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.