Forum Discussion

Mahendran_C_S's avatar
Mahendran_C_S
Helper I
4 years ago

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

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 : Num...
Need Help
Show and Tell
Tips and Tricks
Ehren's avatar
Ehren
Microsoft Employee
4 years ago

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's avatar
Mahendran_C_S
Helper I
4 years ago

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.