Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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 :

 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.

4 REPLIES 4
Microsoft Employee

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

Microsoft Employee

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.

Helper I

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.

Microsoft Employee

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

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors