Phone Words - Interesting Challenge?

[Nick Burns]

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.

 

[cyborgski]

Hi Nick,

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

Thanks,
Mike

 

[Nick Burns]

Interesting... I'm not adept enough to create the custom column you used. Solutions like this helps me expand my knowledge.

 

[cyborgski]

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).

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"

 

[tejonno]

Recursive table solution

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

 

[cyborgski]

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:

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
[B]    AddColumns = List.Accumulate(ListCols, AddNumbers, (s,c) =>[/B]
[B]Table.AddColumn(s,"Column" & Text.From(c), each if c < List.Count([Numbers]) then [Numbers]{c} else  null      )),[/B]
[B]  //Just a shortcut to Merge the columns over and over again[/B]
[B]    MergeTables = List.Accumulate(ListCols, AddColumns, (s,c) =>[/B]
[B]Table.NestedJoin(s, {"Column" & Text.From(c)} ,KeyPad,{"Number"},Text.From(c)      )),[/B]
//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

 

[Nick Burns]

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.

 

[RonaldLieuw]

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

 

[cyborgski]

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

 

[cyborgski]

Glad it helped Nick!