cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mahendran_C_S
Frequent Visitor

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 :

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

 

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.

4 REPLIES 4
Ehren
Microsoft
Microsoft

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
Microsoft
Microsoft

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. 

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.

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, "")

 

mahoneypat_0-1632434859594.png

 

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.