Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello folks!
I feel like I am close to an answer but having Friday brain.
Client has a list of everyone who attended a call.
These calls can be from departments and start with an extension number.
The data returned has no spaces between the names and some names can have multiple capitals (darn my fellow Scots).
So far I have extrated the capitals and the space position.
I feel like I am close but can't put the final pieces together.
Maybe something like ignore a capitals position if it is within 3 characters of the last one.
I want to split everyone on the call out or at least add seperators between them.
Here is the M code using a loaded table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1NC4JAFEX/ysN1LZTMdVoE9rGZwIW4eDlPefCcgTET/32DUYLLyzn33rIMUvuEI75ZK+zgVp8HdhRUmzLIqWkgt4b6OR605wqNFnJ3rq0QXFh3aDJBX4GU0M3iw3aQuYF7WsWUDKgXix/4HrAn6BxNqxiFSbwdfcOOUpDRExS/tUXa76K/NKMrtShwwlYoiZNwYdUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Capital position", each let
Source = [Names],
Length = Text.Length(Source),
Indexes = List.Transform({1..Length}, each if Text.Middle(Source, _ - 1, 1) = Text.Upper(Text.Middle(Source, _ - 1, 1)) and Text.Middle(Source, _ - 1, 1) <> Text.Lower(Text.Middle(Source, _ - 1, 1)) then _ else null),
Result = List.RemoveNulls(Indexes)
in
Text.Combine(List.Transform(Result, each Text.From(_)), ", ")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Space positions", each let
Source = [Names],
Length = Text.Length(Source),
Indexes = List.Transform({1..Length}, each if Text.Middle(Source, _ - 1, 1) = " " then _ else null),
Result = List.RemoveNulls(Indexes)
in
Text.Combine(List.Transform(Result, each Text.From(_)), ", "))
in
#"Added Custom1"
Any help logical or code appreciated!
Sam
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Solved! Go to Solution.
Hi @SamWiseOwl, check this:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1NC4JAFEX/ysN1LZTMdVoE9rGZwIW4eDlPefCcgTET/32DUYLLyzn33rIMUvuEI75ZK+zgVp8HdhRUmzLIqWkgt4b6OR605wqNFnJ3rq0QXFh3aDJBX4GU0M3iw3aQuYF7WsWUDKgXix/4HrAn6BxNqxiFSbwdfcOOUpDRExS/tUXa76K/NKMrtShwwlYoiZNwYdUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Names", type text}}),
Ad_NamesWithSeparator = Table.AddColumn(ChangedType, "Names with Seperator", each
[ a = Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, (x)=> not List.Contains({"a".."z", " "}, x))([Names]), "|"),
b = Text.Replace(a, "Mc|", "Mc")
][b], type text)
in
Ad_NamesWithSeparator
@SamWiseOwl , You can try below code, but not sure since it is Friday evening
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1NC4JAFEX/ysN1LZTMdVoE9rGZwIW4eDlPefCcgTET/32DUYLLyzn33rIMUvuEI75ZK+zgVp8HdhRUmzLIqWkgt4b6OR605wqNFnJ3rq0QXFh3aDJBX4GU0M3iw3aQuYF7WsWUDKgXix/4HrAn6BxNqxiFSbwdfcOOUpDRExS/tUXa76K/NKMrtShwwlYoiZNwYdUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Capital position", each let
Source = [Names],
Length = Text.Length(Source),
Indexes = List.Transform({1..Length}, each if Text.Middle(Source, _ - 1, 1) = Text.Upper(Text.Middle(Source, _ - 1, 1)) and Text.Middle(Source, _ - 1, 1) <> Text.Lower(Text.Middle(Source, _ - 1, 1)) then _ else null),
Result = List.RemoveNulls(Indexes),
FilteredResult = List.FirstN(Result, 1) & List.Select(Result, each _ > List.Last(List.FirstN(Result, 1)) + 3)
in
Text.Combine(List.Transform(FilteredResult, each Text.From(_)), ", ")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Space positions", each let
Source = [Names],
Length = Text.Length(Source),
Indexes = List.Transform({1..Length}, each if Text.Middle(Source, _ - 1, 1) = " " then _ else null),
Result = List.RemoveNulls(Indexes)
in
Text.Combine(List.Transform(Result, each Text.From(_)), ", ")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Names with Separators", each let
Source = [Names],
CapitalPositions = Text.Split([Capital position], ", "),
Positions = List.Transform(CapitalPositions, each Number.FromText(_)),
NamesWithSeparators = Text.Combine(List.Transform(Positions, each Text.Middle(Source, _, 1) & " "), "")
in
NamesWithSeparators)
in
#"Added Custom2"
Proud to be a Super User! |
|
Thank you for the response, sadly that didn't play ball.
The goal on this data would be a split where each black line is.
The final collumn was generated by the above code.
As you say it being a Friday evening makes it hard!
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Hi @SamWiseOwl, check this:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1NC4JAFEX/ysN1LZTMdVoE9rGZwIW4eDlPefCcgTET/32DUYLLyzn33rIMUvuEI75ZK+zgVp8HdhRUmzLIqWkgt4b6OR605wqNFnJ3rq0QXFh3aDJBX4GU0M3iw3aQuYF7WsWUDKgXix/4HrAn6BxNqxiFSbwdfcOOUpDRExS/tUXa76K/NKMrtShwwlYoiZNwYdUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Names", type text}}),
Ad_NamesWithSeparator = Table.AddColumn(ChangedType, "Names with Seperator", each
[ a = Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, (x)=> not List.Contains({"a".."z", " "}, x))([Names]), "|"),
b = Text.Replace(a, "Mc|", "Mc")
][b], type text)
in
Ad_NamesWithSeparator
Looks good thank you. Having to manually replace all the double capitals isn't perfect but its further than I got!
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |