Search code examples
powerbipowerquerypowerbi-desktopm

Vlookup in M language and sum values

In trying to make a Vlookup on PowerQuery that also makes a sum of the multiple values fond. I have 2 tables on my Power BI that are conected by the Report Number as showed below. I need to create a new column on table B that gets the sum of cost at Table A according to their report numbers.

enter image description here

At Power Query I have created a new Column on Table B using the following code:

enter image description here

After that I was planning to simply create a new column summing the list result, but my list is Empty and I can't realize why. Can anyone help me understand why I can't get the results?

I can't do this using DAX, it should be in M

enter image description here

Solution

  • One way to add the column into TableB is:

    = (i)=>List.Sum(Table.SelectRows(TableA, each [Report Num]=i[Report Num]) [Cost])

    Another way is to Group TableA and merge it in. I tend to think this is a faster method for larger tables

    let Source = Excel.CurrentWorkbook(){[Name="TableB"]}[Content],
#"Grouped Rows" = Table.Group(TableA, {"Report Num"}, {{"Cost", each List.Sum([Cost]), type number}}),
#"Merged Queries" = Table.NestedJoin(Source, {"Report Num"},  #"Grouped Rows", {"Report Num"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Cost"}, {"Cost"})
in  #"Expanded Table1"

    of course, if those are the only two columns in TableB, you could just create the whole table in one go

    let Source =  Table.Group(TableA, {"Report Num"}, {{"Cost", each List.Sum([Cost]), type number}})
in Source