Match one column and get the first (min) match in another?

[Anonymous]

This is in power query m language.

I have two tables:

1. prodtable (material number, date, produced qty, delivered qty)
2. material (material number, qty in stock)

The material table is a snapshot from the day I export the data from our system. I call this date seldate. So it simply displays the current quantity in stock.

I want to get the [Quantity in stock] to the prodtable, but it has to work something like this:

• Match each material
• Only put the [Quantity in stock] on the first row that is >= seldate

Any idea how this can be done?

Sample data:

let
    seldate = #date(2020,12,24),
    proddata = #table(
     type table
        [
            #"Material"=text,
            #"Date"=date,
            #"Produced"=number,
            #"Delivered"=number
        ], 
     {
      {123,#date(2020,12,20),5,2},
      {123,#date(2020,12,25),0,10},
      {123,#date(2020,12,26),0,10},
      {123,#date(2020,12,27),0,10},
      {123,#date(2020,12,28),0,10},
      {456,#date(2020,12,20),0,10},
      {456,#date(2020,12,25),0,10},
      {456,#date(2020,12,26),0,10},
      {456,#date(2020,12,27),0,10},
      {456,#date(2020,12,28),0,10},
      {789,#date(2020,12,20),0,10},
      {789,#date(2020,12,25),0,10},
      {789,#date(2020,12,26),0,10},
      {789,#date(2020,12,27),0,10},
      {789,#date(2020,12,28),0,10}
     }
    ),
    
    materialdata = #table(
     type table
        [
            #"Material"=text,
            #"Quantity in stock"=date
        ], 
     {
      {123,5},
      {456,10},
      {789,7}
     }
    )
in
    proddata

[Anonymous]

Hi @Anonymous ,

1. Merge is simple, this is what you'd expect to see.

2. Next step looks into the "merged" table for this line and does to things:

 a. firstDate brings a first date that satisfies your condition of being on or after seldate

 b. Adds a conditional column to "merged" data with a row matching firstDate = Qty all other rows are ignored as requested.

3. The output of step 2 is a column of tables in Custom column. We combine them into one table.

The syntaxis that surprises in step 2: 

Table.AddColumns actually takes a function as the last argument and passes a current row to this function. Usually, it happens implicitly, int this case you use a directive each and refer to it something like _[Data] or simply [Data]. Because we need to refer to it in a nested function we explicitly pass it as x. 

In this expression - 

x[proddata][Date]

prodata represents a table merged to the current row. As x[prodata] returns a table we can legitimately refer to columns in this table, and this is what we do by using x[prodata][Date]. This returns a list of values in the column Date of prodata table filtered to materials matching the current row.

A good read shading more light on this is here: https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let

Kind regards,

JB 

[Anonymous]

@Anonymous That's exactly it. Thank you! 😀

I'm still curious though. Could you try to explain how this works? I mean, what does firstDate do? I haven't seen x[][] before. 

[Fowmy]

@Anonymous 

Can you provide an example or two from the sample data I shared, please?

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

This is how the end result would be. I changed the data a little because all the dates were equal, and I included a commentary field to try and help you understand why the rows will or will not get the Stock Qty. 🙂 

So, the criteria is that Stock Qty will only be placed on each Material's row with the date closest to (bigger than or equal to) seldate. And in this case I've chosen seldate 2020-12-24 (it's not a visible parameter in the picture). 

[Anonymous · Accepted solution]

Hi @Anonymous,

Is this what you are after? Assuming that data are already sorted in the right order, otherwise, add a sort step.

let
    seldate = #date(2020,12,24),
    proddata = #table(
     type table
        [
            #"Material"=text,
            #"Date"=date,
            #"Produced"=number,
            #"Delivered"=number
        ], 
     {
      {123,#date(2020,12,20),5,2},
      {123,#date(2020,12,25),0,10},
      {123,#date(2020,12,26),0,10},
      {123,#date(2020,12,27),0,10},
      {123,#date(2020,12,28),0,10},
      {456,#date(2020,12,20),0,10},
      {456,#date(2020,12,25),0,10},
      {456,#date(2020,12,26),0,10},
      {456,#date(2020,12,27),0,10},
      {456,#date(2020,12,28),0,10},
      {789,#date(2020,12,20),0,10},
      {789,#date(2020,12,25),0,10},
      {789,#date(2020,12,26),0,10},
      {789,#date(2020,12,27),0,10},
      {789,#date(2020,12,28),0,10}
     }
    ),
    
    materialdata = #table(
     type table
        [
            #"Material"=text,
            #"Quantity in stock"=date
        ], 
     {
      {123,5},
      {456,10},
      {789,7}
     }
    ),

    #"Merged Queries" = Table.NestedJoin(materialdata, {"Material"}, proddata, {"Material"}, "proddata", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", (x)=>
                    let 
                        firstDate = List.First(List.Select(x[proddata][Date], each _ >= seldate)),
                        #"Added Custom" = Table.AddColumn(x[proddata], "Stock Qty", each if [Date] = firstDate then x[Quantity in stock] else null)
                    in #"Added Custom"
    ),
    Output = Table.Combine(#"Added Custom"[Custom])
in
    Output

Kind regards,

JB

[az38]

Hi @Anonymous 

I think DAx would be more appropriate solution for your task

[Fowmy]

@Anonymous 

So, where do you get the sedate in the record?
Are you going to enter that date somewhere?, please explain.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

Apologies. Since I would use this in Excel (also), it's a predefined date. For this code it's not relevant how it is set, but in Excel it's a named range. In BI it would be today's date. 🙂

[Fowmy]

@Anonymous 

I create a table for Material,

Merged the Material Table on to Product using the Material Number, then added one custom column to get the value with the condiftion that the Date is >= Today.

let
    Query1 = let
    proddata = #table(
     type table
        [
            #"Material"=text,
            #"Date"=date,
            #"Produced"=number,
            #"Delivered"=number
        ], 
     {
      {123,#date(2020,12,20),5,2},
      {123,#date(2020,12,25),0,10},
      {123,#date(2020,12,26),0,10},
      {123,#date(2020,12,27),0,10},
      {123,#date(2020,12,28),0,10},
      {456,#date(2020,12,20),0,10},
      {456,#date(2020,12,25),0,10},
      {456,#date(2020,12,26),0,10},
      {456,#date(2020,12,27),0,10},
      {456,#date(2020,12,28),0,10},
      {789,#date(2020,12,20),0,10},
      {789,#date(2020,12,25),0,10},
      {789,#date(2020,12,26),0,10},
      {789,#date(2020,12,27),0,10},
      {789,#date(2020,12,28),0,10}
     }
    )
    
in
    proddata,
    #"Changed Type" = Table.TransformColumnTypes(Query1,{{"Material", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Material"}, material, {"Material"}, "Qty", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Stock Qty", each if [Date] >= Date.From(DateTime.LocalNow()) then [Qty][Quantity in stock]{0} else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Qty"})
in
    #"Removed Columns"

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

@Fowmy That's a good start! However, the issue is that I only what the stock quantity to be placed on the first date >= seldate. That's the part I can't resolve.