Results 1 to 10 of 10

Thread: Phone Words - Interesting Challenge?

  1. #1
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    67
    Articles
    0
    Excel Version
    Office 365

    Phone Words - Interesting Challenge?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Take any Phone # and convert it to all possible Letters aka Phone Words.
    Phone # can be input any format (strip out all other characters)

    Example:

    536-7857 = Ken Puls, etc...


    I'm working on my own solution now.
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    54
    Articles
    0
    Excel Version
    Office 365
    Hi Nick,

    Is this what you were looking for? Fun challenge if it was!

    Thanks,
    Mike
    Attached Files Attached Files

  3. #3
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    67
    Articles
    0
    Excel Version
    Office 365
    Interesting... I'm not adept enough to create the custom column you used. Solutions like this helps me expand my knowledge.
    Oh... by the way, YOU'RE WELCOME!

  4. #4
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    54
    Articles
    0
    Excel Version
    Office 365
    I actually do not like that column. I rewrote to eliminate it. I am now just using the List.Accumulate to repeatedly add or expand a column. Using List.Accumulate reduces the number of visible steps and allows for variable number of phone digits (7,10 or international).


    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="PhoneNumbers"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"PhoneNumber", type text}}),
        AddNumbers = Table.AddColumn(#"Changed Type", "Numbers", each List.Transform(List.Select(Text.ToList([PhoneNumber]), each "0"<= _ and _ <= "9"), Number.From), type list),
        MaxCols = List.Max(List.Transform(AddNumbers[Numbers], List.Count))-1,
      //Just a shortcut to add the columns over and over again
        AddTables = List.Accumulate({0..MaxCols}, AddNumbers, (s,c) =>
    Table.AddColumn(s,Text.From(c), each if c < List.Count([Numbers]) then Table.SelectRows(KeyPad, (row) => row[Number] = [Numbers]{c}) else  null      )),
    //Just a shortcut to expand the columns over and over again
        ExpandAllTables = List.Accumulate({0..MaxCols}, AddTables,(s,c) =>
    Table.ExpandTableColumn(s,Text.From(c), {"Letters"}, {Text.From(c)})),
        #"Merged Columns" = Table.CombineColumns(ExpandAllTables,List.Transform({0..MaxCols},  Text.From),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Words"),
        #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Numbers"})
    in
        #"Removed Columns"

  5. #5
    Neophyte tejonno's Avatar
    Join Date
    Sep 2019
    Location
    Wellington, NZ
    Posts
    1
    Articles
    0
    Excel Version
    2016

    Recursive table solution

    Here's my attempt, not asPowerQuery Challenge 7 - Phone numbers.xlsx elegant as yours cyborgski, but I learnt about recursively passing a table so that was kinda neat...

  6. #6
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    54
    Articles
    0
    Excel Version
    Office 365
    Hi tejonno,

    I have learned a couple of things from your solution.

    1) I did not realize that the Excel.CurrentWorkbook function will also return named ranges.

    2) I use the technique below of adding a table to a column with select rows quite often.
    Table.AddColumn(s,Text.From(c), each if c < List.Count([Numbers]) then Table.SelectRows(KeyPad, (row) => row[Number] = [Numbers]{c}) else null ))

    I have found it to be useful but slow compared to the NestedJoin. Obviously, performance is not a problem for a dataset of this size. But looking at your solution made me realize that I could List.Accumulate out the columns and then List.Accumulate a nested join. This technique could come in handy for me in the future.
    Below is the change inspired by your code:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="PhoneNumbers"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"PhoneNumber", type text}}),
        AddNumbers = Table.AddColumn(#"Changed Type", "Numbers", each List.Transform(List.Select(Text.ToList([PhoneNumber]), each "0"<= _ and _ <= "9"), Number.From), type list),
        MaxCols = List.Max(List.Transform(AddNumbers[Numbers], List.Count))-1,
        ListCols = List.Buffer({0..MaxCols}),
        //Just a shortcut to expend the list out by columns
        AddColumns = List.Accumulate(ListCols, AddNumbers, (s,c) =>
    Table.AddColumn(s,"Column" & Text.From(c), each if c < List.Count([Numbers]) then [Numbers]{c} else  null      )),
      //Just a shortcut to Merge the columns over and over again
        MergeTables = List.Accumulate(ListCols, AddColumns, (s,c) =>
    Table.NestedJoin(s, {"Column" & Text.From(c)} ,KeyPad,{"Number"},Text.From(c)      )),
    //Just a shortcut to expand the columns over and over again
        ExpandAllTables = List.Accumulate(ListCols, MergeTables,(s,c) =>
    Table.ExpandTableColumn(s,Text.From(c), {"Letters"}, {Text.From(c)})),
        #"Merged Columns" = Table.CombineColumns(ExpandAllTables,List.Transform(ListCols,  Text.From),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Words"),
        #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"PhoneNumber", "Words"})
    in
        #"Removed Other Columns"

    Thank you,
    Mike

  7. #7
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    67
    Articles
    0
    Excel Version
    Office 365
    Mike - that is slick - in one of my solutions I got as far as getting each number into it's own column, but wasn't sure how to easily merge the letters to each number. I know I could have done it manually, but then the phone number would have to be a strict number length and I was obviously trying to avoid that.
    Oh... by the way, YOU'RE WELCOME!

  8. #8
    Seeker RonaldLieuw's Avatar
    Join Date
    Sep 2019
    Posts
    7
    Articles
    0
    Excel Version
    2010
    Hi guys, I have tried to follow (some of) your solutions but got lost at the start. But I was wondering what "real live" business problems can I solve with your different ideas?
    Ronald

  9. #9
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    54
    Articles
    0
    Excel Version
    Office 365
    Good question Ronald,

    I have used List.Accumulate to calculate amortization/accretion schedules for journal entry support , work with bills of material, manage default parameters for large import templates, handle file formats where the headers changed order between files and process fixed format reports.

    Prior to Nick's challenge, I have never used it more than once in a query. And certainly never thought to use it to NestJoin or ExpandTables over multiple columns. I don't know if I will ever use those techniques again, but they are there if I need them. And it was fun to figure out.

    Regards,
    Mike

  10. #10
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    54
    Articles
    0
    Excel Version
    Office 365
    Glad it helped Nick!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •