Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I need to do a self-table look up with the following features:
Say it I have three columns like this (they are simplified, real data are more complex):
COLUMN1 COLUMN2 COLUMN3
A1 a1 null
A2 a2 null
B1 b1 A1
B2 b2 A1
B3 b3 A2
C1 c1 B3
C2 c2 B1
C3 c3 B1
C4 c4 B4
...and so on
I need to get a COLUMN4 which takes COLUMN3 values, seach for them in COLUMN1 and get COLUMN2 as the output. With DAX that is as simple as LOOKUPVALUE(COLUMN2,COLUMN1,COLUMN3), but I can not figure it out how to do it with M. The output should look like:
COLUMN1 COLUMN2 COLUMN3 COLUMN4
A1 a1 null null
A2 a2 null null
B1 b1 A1 a1
B2 b2 A1 a1
B3 b3 A2 a2
C1 c1 B3 b3
C2 c2 B1 b1
C3 c3 B1 b1
C4 c4 B4 b4
...and so on
It is highly important to do it in the same table, trying to avoid creating a new one from a "merge" or something like that, since I have to make further transformations.
Thank you very much for your help.
-J
Solved! Go to Solution.
Hi @Anonymous
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzBDcAgDAN3yZtPQxdoGAPxKFmMWZiMEsuqxCsnn+Na5bnmkCRzvABp6QuVof6hsdkB8RmC7a6HyBSAmN2icMoBUQzBKQcYpwqnPB/ipgDYPq0t", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Column4", each try if [Column3] = "" then null else List.Range(#"Changed Type"[Column2], List.PositionOf(#"Changed Type"[Column1], [Column3]), 1){0} otherwise "Not found", type text)
in
#"Added Custom"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
I do not get any errors. You're probably doing something differently. See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
I do not get any errors. You're probably doing something differently. See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Anonymous
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzBDcAgDAN3yZtPQxdoGAPxKFmMWZiMEsuqxCsnn+Na5bnmkCRzvABp6QuVof6hsdkB8RmC7a6HyBSAmN2icMoBUQzBKQcYpwqnPB/ipgDYPq0t", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Column4", each try if [Column3] = "" then null else List.Range(#"Changed Type"[Column2], List.PositionOf(#"Changed Type"[Column1], [Column3]), 1){0} otherwise "Not found", type text)
in
#"Added Custom"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Table.AddColumn(NameOfPriorStep, "NewColumnName", each if List.Contains([Column3], [Column1]) then [Column1] else null)
--Nate
Hi there,
this solution is quite similar to:
https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/
but in both cases, the editor gives me the next output Error:
Expression.Error: We cannot convert the value "X" to type List.
Value = "X"
Type = [Type]
where "X" is each row value.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |