Sorry if i wasn't being clear enough.
I have a table called ABC with a column called ActivityID, and in it there's data like below
1111
1231
4321
1.2.3.4
5.3.2.1
5.3.9.3
I would like to convert those numbers without dots, e.g. 1111 to show as 1.1.1.1
I followed your instructions above and created a blank query and copied the code in it.
Then i went back to the ABC table and created a custom column called 'Custom' and changed the DATA table to ActivityID as shown below:-
Table.AddColumn(
Source,
"With Dots",
each
let
varLength = Text.Length([ActivityID]),
varData = [ActivityID]
in
Text.BeforeDelimiter(
Text.Combine(
List.Generate(
()=> [x=0],
each [x] < varLength,
each [x = [x]+1],
each Text.Middle(varData, [x], 1) & "."
)
),
".", varLength-1
),
type text
)
and the result is as below:-
Did i do it wrongly ?
If what you show is what you have, a simple one line algorithm suggests itself
Before
- Transform the table column
- create a List of the characters in the cell
- remove any existing dots
- Recombine the text using the dot as the delimeter
#"Dotted" = Table.TransformColumns(#"Previous Step",
{"ActivityID", each Text.Combine(List.RemoveMatchingItems(Text.ToList(_),{"."}),".") })To Reproduce, place the following into a blank query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQCpVgdIMPI2ATMMDE2goroGekZ60EETYEsIz1DONtSz1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ActivityID = _t]),
#"Previous Step" = Table.TransformColumnTypes(Source,{{"ActivityID", type text}}),
#"Dotted" = Table.TransformColumns(#"Previous Step",
{"ActivityID", each Text.Combine(List.RemoveMatchingItems(Text.ToList(_),{"."}),".") })
in
#"Dotted"After
The 2nd line that says Source in your code. That should be the last line before this Add Columns. It might be #"Changed Type" or something similar. Those steps are on this page Utilizing M Code Samples Given as Solutions in Pow... - Microsoft Power BI Community I had linked to.
Thanks ! It worked, however, the values that already have 'dots', have been added more dots...as shown in the image below
Try this code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlGK1YlWAtKmYIahobExmGFiZGioFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
DottedData =
Table.AddColumn(
Source,
"With Dots",
each
let
varLength = Text.Length([Data]),
varData = [Data]
in
Text.BeforeDelimiter(
Text.Combine(
List.Generate(
()=> [x=0],
each [x] < varLength,
each [x = [x]+1],
each Text.Middle(varData, [x], 1) & "."
)
),
".", varLength-1
),
type text
)
in
DottedData
It returns this:
What it is doing:
varLength stores the length of the string
varData stores the value in the [Data] column to be used later
List.Generate loops through as many times as is the length (varLength) of the string, pulls out each character (that is what Text.Middle() is doing) and adds a period.
It generates a list of items, {1., 2., 3.} etc.
Text.Combine converts that list into one string, so 1.2.3.4.
Finally, Text.BeforeDelimiter pulls all data before that final period, so 1.2.3.4
I tested this with varying lengths. It will work.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
