Results 1 to 10 of 10

Thread: Phone Words - Interesting Challenge?

Hybrid View

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

    Phone Words - Interesting Challenge?

    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

Posting Permissions

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