Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KLJ
Helper I
Helper I

Join two tables

Hello,

 

I have two tables. Table one contains sales orders and table two items on stock.

In table one the same item can be on more then one sales order. If this is the case then the oldest sales order (lowest sales order no.) gets the item.

 

Example:

Table one contains sales orders:

Sales order noItem noQTY on sales order
1A13
2S14
3S12
4A25

 

Table two contains the stock:

Item NoQTY
A15
A210
S15
S23

 

What I need is a way to join the two tables so I can get this:

Sales order noItem noQTY on sales orderQTY On Stock
1A133
2S144
3S121
4A255

 

If the QTY on Stock is higher then the QTY on the sales order then it shall only show the QTY corresponding to the QTY on the sales order.

Hope it is not too confusing 🙂

 

Is there any way this can be done?

 

Best regards

Kim Ladelund

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

This would be easier in DAX, but it is possible in M too.

 

A key piece is to create a new column that records the cumulative sum of sales of each item. To do this, refer to this prior thread:
https://community.powerbi.com/t5/Desktop/How-to-do-a-running-Sum-by-group-in-Power-Query/m-p/290123

 

Supposing you have that, then you can merge with the Stock table to get the QTY and then write some logic to put it all together. Here's an expanded example similar to yours:

AlexisOlson_0-1651175558318.png

 

Here's the M query you can use to reproduce the above (assuming you have the Stock table you defined as a separate query):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEEWZAbKAUqxOtZARkBYOEjOBCxshCRmAhE2QhE7CQKcgsENcCrtEMJgQRBgmZw4RAhhpClIE0BBuhWGkJEzKGWwlUjKTMVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales order no" = _t, #"Item no" = _t, #"QTY on sales order" = _t, PrevSales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales order no", Int64.Type}, {"QTY on sales order", Int64.Type}, {"PrevSales", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Item no"}, Stock, {"Item No"}, "Stock", JoinKind.LeftOuter),
    #"Expanded Stock" = Table.ExpandTableColumn(#"Merged Queries", "Stock", {"QTY"}, {"QTY"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Stock", "QTY On Stock", each
        if [QTY on sales order] + [PrevSales] > [QTY]
        then (if [QTY] > [PrevSales] then [QTY] - [PrevSales] else 0)
        else [QTY on sales order], Int64.Type)
in
    #"Added Custom1"

Note that this does not include the running total logic.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

This would be easier in DAX, but it is possible in M too.

 

A key piece is to create a new column that records the cumulative sum of sales of each item. To do this, refer to this prior thread:
https://community.powerbi.com/t5/Desktop/How-to-do-a-running-Sum-by-group-in-Power-Query/m-p/290123

 

Supposing you have that, then you can merge with the Stock table to get the QTY and then write some logic to put it all together. Here's an expanded example similar to yours:

AlexisOlson_0-1651175558318.png

 

Here's the M query you can use to reproduce the above (assuming you have the Stock table you defined as a separate query):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEEWZAbKAUqxOtZARkBYOEjOBCxshCRmAhE2QhE7CQKcgsENcCrtEMJgQRBgmZw4RAhhpClIE0BBuhWGkJEzKGWwlUjKTMVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales order no" = _t, #"Item no" = _t, #"QTY on sales order" = _t, PrevSales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales order no", Int64.Type}, {"QTY on sales order", Int64.Type}, {"PrevSales", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Item no"}, Stock, {"Item No"}, "Stock", JoinKind.LeftOuter),
    #"Expanded Stock" = Table.ExpandTableColumn(#"Merged Queries", "Stock", {"QTY"}, {"QTY"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Stock", "QTY On Stock", each
        if [QTY on sales order] + [PrevSales] > [QTY]
        then (if [QTY] > [PrevSales] then [QTY] - [PrevSales] else 0)
        else [QTY on sales order], Int64.Type)
in
    #"Added Custom1"

Note that this does not include the running total logic.

Thanks for the help 😊

It works!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors