Excel - Power Query 2016

0
votes

I got data from two tables.

Customers (containing customer ID and the total value of orders/funding

Orders (Containing customer ID and each order)

enter image description here

I created a Power Query, then chose the option to "Merge Queries as New". Selected the matching Columns (Customer ID) and chose the option:Left Outer (All from the first and, matching from second => All from the customer table, matching from the order table). Then I expanded the last column of the Query to include what I wanted from the Order table resulting in the table below on the left. The one on the right is what I'm after. The problem is that funding amounts are already totals per customer. I don't need the value of each order broken down. I still need the orders displayed but I don't need their values (just the total per customer). Is it possible to do it like the one below on the right? Otherwise, the grand total is way off.

enter image description here

2
excelpowerquery

2 Answers

1
votes

I think what you're trying to do is join with only the first instance of each value in your Customer column. There doesn't appear to be any feature or GUI element that allows you to do that (I had a look at the reference documentation for Power Query M, maybe I missed something).

To replicate your data, I'm starting off with some tables (left table is namedCustomers, right table is namedOrders):

My tables

I then use the M code below (the first few lines are just to get my tables from the sheet): 

let
    customers = Excel.CurrentWorkbook(){[Name = "Customers"]}[Content],
    orders = Excel.CurrentWorkbook(){[Name = "Orders"]}[Content],
    merged = Table.NestedJoin(orders, {"CUSTOMER"}, customers, {"CUSTOMER"}, "merged", JoinKind.LeftOuter),
    indexColumn = Table.AddIndexColumn(merged, "Temporary", 0, 1),
    indexes =
        let
            uniqueCustomers = Table.Distinct(Table.SelectColumns(indexColumn, {"CUSTOMER"})), // Want to keep as table
            listOfRecords = Table.ToRecords(uniqueCustomers),
            firstOccurenceIndexes = List.Accumulate(listOfRecords, {}, (listState, currentItem) =>
                List.Combine({listState, {Table.PositionOf(indexColumn, currentItem, Occurrence.First, "CUSTOMER")}})
            )
        in
            firstOccurenceIndexes,
    expandSelectively =
        let
            toBoolean = Table.TransformColumns(indexColumn, {{"Temporary", each List.Contains(indexes, _), type logical}}),
            tableOrNull = Table.AddColumn(toBoolean, "toExpand", each if [Temporary] then [merged] else null),
            dropRedundantColumns = Table.RemoveColumns(tableOrNull, {"merged", "Temporary"}),
            expand = Table.ExpandTableColumn(dropRedundantColumns, "toExpand", {"FUNDING"})
        in
            expand
in
    expandSelectively

If your table names and column names match mine (including case sensitivity), then you might just be able to copy-paste all of the M code above into the Advanced Editor and have it work for you. Otherwise, you may need to tweak as necessary.

This is what I get when I load the query to the worksheet.

Query loaded

There might be better (more efficient) ways of doing this, but this is what I have for now.

0
votes

If you're not using the order ID column, then I would suggest doing a Group By on the OrderTable before merging in the funding so that you'd end up with a table like this instead:

Region  Customer  OrderCount  Funding
South   A         3           2394
South   B         2           4323
South   C         1           1234
South   D         2           3423

This way you don't have mixed levels of granularity that cause problems like you are seeing with the totals.