Forum Discussion

Tuan's avatar
Tuan
Helper III
7 years ago
Solved

Summing two tables based on column

I have two tables, Orders and Invoices. The Invoice table has both Order Number and Invoice Number.   I'm trying to get the  Sum of Sales from the Invoice Table + the sum of sales not yet invoiced ...
Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    This M code works

     

    let
    Source = Table.Combine({Invoices, Orders}),
    Partition = Table.Group(Source, {"Sales Order"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Invoice Order", "Sales", "Index"}, {"Invoice Order", "Sales", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Partition", each ([Index] = 1)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Invoice Order", "Sales Order", "Sales", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Invoice Order", "Invoice Number"}, {"Sales", "Sales + outstanding"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales + outstanding", type number}})
in
    #"Changed Type"

    Hope this helps.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

This M code works

 

let
    Source = Table.Combine({Invoices, Orders}),
    Partition = Table.Group(Source, {"Sales Order"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Invoice Order", "Sales", "Index"}, {"Invoice Order", "Sales", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Partition", each ([Index] = 1)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Invoice Order", "Sales Order", "Sales", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Invoice Order", "Invoice Number"}, {"Sales", "Sales + outstanding"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales + outstanding", type number}})
in
    #"Changed Type"

Hope this helps.

 

Tuan's avatar
Tuan
Helper III
7 years ago

That works and I did something similiar, trying to figure out a a dax solution instead of flattening the tables.