Conditional column: compare column table1 against table2, if match return value of a column table2

[DAXRichArd]

Hello,

I don't know M.Query, however I hack and try find solutions on my own.

Desired Outcome

Similar to VLOOKUP in Excel:

• Table 1 (fact table)  = F-Cargo
• Table 2 (dimenstion table) = D-Airilnes
• Compare Table 1 column [complany_name] against Table 2 column [company_name].
  • If match return to Table1 value of Table2 column [airlines].
    • Note Table2 to has only 2 columns: [company_name] and [airlines].

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

[DAXRichArd]

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

[adudani]

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.