Map data via a many-to-many Index (excel = sumifs, vlookup, DAX=calculate+filter) RRS feed

  • Question

  • Hello all,

    I want to map the value field from Table 1 to Table 2 using an Index. 

    I know how to do this in Dax/PowerPivot, but can't due to space constraints. (my actual database is larger than the example below).  I know how to do this in Excel, but obviously can't (due to space).  

    How do I do it in Power Query/M? I will then load my data into Power Pivot and go from there.

    Table 1
    3 Columns. 13m rows
    Field A (130 unique values)
    Field C (100k unique values)

    2 Columns. 780 rows. Many-to-many
    Field A (130 unique values)
    Field B (107 unique values)

    Table 2
    3 Columns. 10.7m rows
    Field B (107 unique values)
    Field C (100k unique values)
    Value (summed, via Table 1 x Index)


    Any help is MUCH appreciated.

    Kind Regards

    Wednesday, May 17, 2017 10:00 AM