Query Between Two Tables & Add Additional Lines

[Anonymous]

I have two excel spreadsheets that I have loaded up into Power Bi.  One is a ref table that lists all the zip codes within a circuit and the multiplier that needs to be applied to column "E" and "F" in the second spreadsheet.

The table on the right does not contain Zip Code.  Can Power Bi input lines base on circuit to add zip code?  Then if I can do that, I need the multiplier times the "CI" & "CMI" based on the circuit and the zip code in the table on the left.

We tried the lookupvalue but we were unsuccessful due to multiple circuit numbers and think calculate could work but unsure of the input functionality.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. In Power Query -- Create Indices for the two tables -- Add Column – Index Column – From 1.

2. Create calculated column.

Zip Code =
MAXX(
    FILTER(ALL(Table1),
    'Table1'[Index]=EARLIER('Table2'[Index])),[Zip Code])

Column =
VAR _CI=
SUMX(
    FILTER(ALL('Table2'),
    'Table2'[Zip Code]=EARLIER('Table1'[Zip Code])),[CI])
VAR _CMI=
SUMX(
    FILTER(ALL('Table2'),
    'Table2'[Zip Code]=EARLIER('Table1'[Zip Code])),[CMI])
RETURN
_CI * _CMI

2. Result:

Whether it meets your expected results, if not, you can express their expected results in the form of pictures, and we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly