Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
Hi @ryanb11 ,
sorry, I'm very busy currently. Have adjusted the code below.
It currently only works on a 1-column-table. Please let me know if you need it differently.
let
Source = Rels,
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Feedback], " ")),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Custom.1",
each Text.Combine(
List.Select(
[Custom],
(l) => [
ListOfCharacters = Text.ToList(l),
Result = not (List.Count(ListOfCharacters) >= 10
and List.AllTrue(
List.Transform(ListOfCharacters, (x) => List.Contains({"0".."9"}, x))
)
)][Result]
), " ")
)
in
#"Added Custom1"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ryanb11 Can the phone numbers be anywhere in the text? Can they have different formats like 555-555-5555 or (555)-555-5555? Do you want DAX or Power Query or either?
@Greg_Decklerthe phone numbers will always be in the format in the examples i sent, so 11 digits, but i want to account for a mistake in writing so want to try and include 1o digits if possible. the phone numbers can appear anywhere in the text, at the beginning middle or end. My power query column nearly works so may be best to amend that? but a solution any way is very welcome!
@ImkeF @edhans got any Power Query tricks for this?
Hi @ryanb11 ,
this is a bit of a mouthful, but it works:
let
Source = #table(
{"Column1"},
List.Zip(
{{
"Call me on 08588812885",
"Call me on 07525812845",
"I need assitance please call me",
"Last tried in 2019"
}}
)
),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Column1], " ")),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Custom.1",
each Text.Combine(
List.Select(
[Custom],
(l) => [
ListOfCharacters = Text.ToList(l),
Result = not (List.Count(ListOfCharacters) >= 10
and List.AllTrue(
List.Transform(ListOfCharacters, (x) => List.Contains({"0".."9"}, x))
)
)][Result]
), " ")
)
in
#"Added Custom1"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
are stored in a column, so from your query i have removed the hard coded text with the column.
This gave me an error saying it cannot convert the values in the column to type list
@ImkeFthanks for your help so far, any ideas on this?
Hi @ryanb11 ,
not sure I understand, but you have to replace everything in the first step "Source" by a reference to your existing table.
Otherwise please paste used code or picture so I understand what's going on.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF
I am having trouble changing the source to my source table, I am looking at what needs to be changed but I keep getting errors.
It my source table is called 'Rels' and the source column with the open text including phone numbers is 'Feedback', would you be able to write the code to include this?
Thanks,
Hi @ryanb11 ,
sorry, I'm very busy currently. Have adjusted the code below.
It currently only works on a 1-column-table. Please let me know if you need it differently.
let
Source = Rels,
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Feedback], " ")),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Custom.1",
each Text.Combine(
List.Select(
[Custom],
(l) => [
ListOfCharacters = Text.ToList(l),
Result = not (List.Count(ListOfCharacters) >= 10
and List.AllTrue(
List.Transform(ListOfCharacters, (x) => List.Contains({"0".."9"}, x))
)
)][Result]
), " ")
)
in
#"Added Custom1"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ryanb11,
I think ImkeF 's formulas should help for your scenario, did these code works on your side now?
If they still not work, it will be helpful if you provide some dummy data you worked on. We can directly test coding formula on them. (notice: please not attach sensitive data into the sample records)
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |