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,
I don't know M.Query, however I hack and try find solutions on my own.
Desired Outcome
Similar to VLOOKUP in Excel:
I attempted the following code:
= Table.AddColumn(#"Added Column dom_int", "TEST", each if [company_name] = #"D-Airlines [company_name]" then #"D-Airlines [airlines]" else "NO MATCH")
Again, I don't know what I'm doing. However I hacked attempting to solve for "x".
Thx in advance for all you help.
Table Examples
Sorry about the tables. When I post I get an HTML warning that I don't know how to correct. It converts my multiple columns into one.
F-Cargo
Date | weight | company_name | "new column I wish to create" |
1/1/2023 | 123,456 | DAXRichard wide body | should return DAXRichard from table D-Airlines below. |
2/4/2023 | 2,548 | DAXRichard express |
D-Airlines
company_name | airlines |
DaxRichard wide body | DAXRichard |
DaxRichard express | DAXRichard |
NoWhere Airlines unlimited | NoWhere Airlines |
NoWhere Airlines international | NoWhere Airlines |
NoWhere Airlines name iteration 7000 | NoWhere Airlines |
Hi adudani,
Thank you for your reply.
I tried your solution and it did not work for me.
Now that my head is clear I was able to find a solution through DAX.
I stepped out of query and I used LOOKUPVALUE in the DAX environment.
See also 4 Ways of Getting Column from one table to another in Power BI https://www.youtube.com/watch?v=b1QtgQfwN04
Big thanks for giving me your time.
Have a great day!
DAXRichard
HI @DAXRichArd,
create two blank queries in PQ, paste the below codes into the advanced editor:
D-Airlines
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckmsCMpMzkgsSlEoz0xJVUjKT6lU0lE6tEABiFwcI6CSSrE6KGpTKwqKUouLcan0yw/PSC1KVXDMLMrJzEstVijNy8nMzSxJTYHrQFeCXV9mXklqUV5iSWZ+XmIOiXrzEnNTFYB2FoG1K5gbGBjgMSEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [company_name = _t, #" airlines" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"company_name", type text}, {" airlines", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{" airlines", Text.Trim, type text}, {"company_name", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{" airlines", Text.Clean, type text}, {"company_name", Text.Clean, type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Cleaned Text",{{" airlines", Text.Upper, type text}, {"company_name", Text.Upper, type text}})
in
#"Uppercased Text"
F-Cargo
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lE6tEABGRkaGeuYmJphSrg4RgRlJmckFqUolGempCok5adUKsXqRCsZ6ZvgMMtIx9TEAq9JqRUFRanFxUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #" weight" = _t, #" company_name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {" weight", Int64.Type}, {" company_name", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{" company_name", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{" company_name", Text.Clean, type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Cleaned Text",{{" company_name", Text.Upper, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Uppercased Text", {" company_name"}, #"D-Airlines", {"company_name"}, "D-Airlines", JoinKind.LeftOuter),
#"Expanded D-Airlines" = Table.ExpandTableColumn(#"Merged Queries", "D-Airlines", {" airlines"}, {"D-Airlines. airlines"})
in
#"Expanded D-Airlines"
Appreciate a thumbs up if this is helpful.
Please let me know if this resolves the question.
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.