Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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}})
Solved! Go to 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"
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.
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"
Tx for your appreciated help @FrankAT !! My apologies for my tardive reply. Yours sincerely,
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |