Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Add new calculated column with values from 2 tables (Power Query)

Hello all,    I'm really new to Power BI and Power Query and I am now struggling with the following:   Imagine we have Table1 and Table2, which can link by the column "Name":   Table1 Nam...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    Try adding this as a new custom column in Table2:

     

    let
  currentName = [Name],
  meters = Table.SelectRows(Table1, each [Name] = currentName){0}[Produced Meters]
in
  meters / [Run Time]

     

     

    This gives me the following output:

     

    Pete

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

It's certainly possible to do this in one step (see BA_Pete's answer) but you should be aware that such a solution is less efficient than a multi-step approach of merging in the column and then dividing.

 

Merge-then-divide should be a linear complexity O(N) whereas an unoptimized lookup-then-divide is likely quadratic complexity O(N2). If your dataset is large this can make a huge difference.

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

     

    AlexisOlson is correct.

    I've done the solution as requested as an excercise but, if it was up to me, I'd relate both tables in the data model to a Name dimension table and do it with measures.

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I know you already helped me, but could you show me with this example how this would be done? 

      I've been working mainly in the Power Query since I don't have much familiarity with DAX and it still kind of confuses me.

       

      Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you. I will take this into account and use Merge-then-divide