Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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
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, "")
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
72 | |
67 | |
42 | |
28 | |
20 |