Converting Number column to binary and map the binary 1's based on position with other table/values

[Mahendran_C_S]

I have a column with data type whole number with numbers like 39,191,68 etc... I have a requirement where I have to map these values with another table or list. 
The mapping table contains :

Number	Country Name
1	India
2	Canada
3	France
4	USA
5	UAE
6	Singapore
7	Malaysia

Eg. How this mapping works with value 39 is 
1. Have to convert the number 39 into binary 100111 and start mapping for bit 1's from the right side position.

2. Let's say for this 100111 starting from rightmost position side 1 maps to the mapping table with number 1  where the country is India and when bit 1 occurs in 2nd position from the right side it maps to country Canada and so on.

Finally, I want my column to be like the mapped string instead of the number 39.

39 - India,Canada,France,Singapore 

Could , anyone help me out to solve this requirement using power query M language in the power query editor or using DAX?
Preferably looking to solve in the power query editor.

[Ehren]

Hi @Mahendran_C_S. Here's a full example using the sample data you provided. There are three separate queries (Numbers, toBinaryPositions, and Countries). For each query, you can create a Blank Query in the PQ Editor, name it as shown in the comment, and then paste the code for that query into the Advanced Editor.

// Numbers
let
    Source = #table(type table [Number=number], {{39}, {78}}),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "BinaryPositions", each toBinaryPositions([Number]), type list),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "BinaryPositions"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"BinaryPositions"}, Countries, {"Number"}, "Countries", JoinKind.LeftOuter),
    #"Expanded Countries" = Table.ExpandTableColumn(#"Merged Queries", "Countries", {"Country Name"}, {"Country Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Countries",{"BinaryPositions"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Index"}, {{"Number", each List.Max([Number]), type number}, {"Rows", each _, type table [Number=number, Index=number, Country Name=nullable text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Country Names", each Text.Combine([Rows][Country Name], ",")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index", "Rows"})
in
    #"Removed Columns1"

// toBinaryPositions
let
    Source = (val) =>
let
    div = List.Generate(() => val, (x) => x >= 1, (x) => Number.RoundDown(x / 2)),
    mod = List.Transform(div, each Number.Mod(_, 2)),
    pos = List.Select(List.Positions(mod), each mod{_} = 1)
in
    List.Transform(pos, each _ + 1)
in
    Source

// Countries
let
    Source = #table(
    type table [Number=number, Country Name=text],
    {
        {1, "India"},
        {2, "Canada"},
        {3, "France"},
        {4, "USA"},
        {5, "UAE"},
        {6, "Singapore"},
        {7, "Malaysia"}
    }
)
in
    Source

[Ehren]

Here's some M code that converts a number to a list of binary ones and zeroes, then returns the position+1 for all the ones.

let
    val = 39,
    div = List.Generate(() => val, (x) => x >= 1, (x) => Number.RoundDown(x / 2)),
    mod = List.Transform(div, each Number.Mod(_, 2)),
    pos = List.Select(List.Positions(mod), each mod{_} = 1)
in
    List.Transform(pos, each _ + 1)

Hopefully you can do the rest. 

[Mahendran_C_S]

Since I am a beginner to power query M language.I was not able to achieve the requirement with the above solution.  In the above solution, a value is passed and the final result is displayed in the list. 
What I wanted is I have to transform the entire column with the mapping country name as string/text .

For the column below :

Number

39

78

I want my resultant column to be :

Resultant column

India,Canada,France,Singapore

Canada,France,USA,Malaysia

I don't know how to map the values with the country name and display them in the resultant column as a string . Could you please help me out in solving this? Thanks in advance.

[mahoneypat]

Nice approach, @Ehren .  I modified your code to a function, so it can be used in a custom column with the column of numbers and return the concatenated 1s and 0s.

(num)=>
let
val = num,
div = List.Generate(() => val, (x) => x >= 1, (x) => Number.RoundDown(x / 2)),
mod = List.Transform(List.Reverse(div), each Number.ToText(Number.Mod(_, 2)))
in
Text.Combine(mod, "")

Pat