Forum Discussion

DAXRichArd's avatar
DAXRichArd
Resolver I
3 years ago

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

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
Need Help

2 Replies

  • adudani's avatar
    adudani
    Memorable Member
    3 years ago

    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.

  • DAXRichArd's avatar
    DAXRichArd
    Resolver I
    3 years ago

    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