Power Query - Nested IF function

Question

I am currently working in Power Query and trying to determine a specific field to output based on multiple conditions.

I have a table as follows:

What we can see is that we have multiple rows per order und 2 status keys. I need to determine that MAX Date based on this logic per item:

As we can see, each type has a specific key combination to get the MAX Date. The result would be like this:

Order 100 is 04.02.2021

Is there any alternative to a very long IF Clause? There are 7 Product types in total each with specific key combinations.

How would you do that? In Power Query.

Please explain better. Why is Pipes 50/30 better than Pipes 50/40. 40>50. Is Key2 not relevant? — horseyride
– horseyride, Commented Jan 9, 2022 at 14:03
Is the result table maybe wrong? If I understand it correctly Order 100 should be 04.02.2021, am I wrong? — Marco_CH
– Marco_CH, Commented Jan 9, 2022 at 15:23

Ron Rosenfeld · Accepted Answer · 2022-01-10 00:56:54Z

Just Join the two tables on the relevant keys:

let

//read original data and set data types
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Order", Int64.Type}, {"Product Type", type text}, 
        {"Status Key 1", Int64.Type}, {"Status Key 2", Int64.Type}, 
        {"Date", type date}}),

//specification table
//hard coded here but 
//     could read it in from Excel instead
    spec = Table.FromColumns({
     {"Metals","Pipes"},
     {80,50},
     {30,30}},
     type table[Material=text, Key1 = Int64.Type, Key2=Int64.Type]),

//now just do the join and remove the unneeded columns
    join = Table.Join(#"Changed Type",{"Product Type","Status Key 1","Status Key 2"}, spec,{"Material","Key1","Key2"},JoinKind.RightOuter),
    #"Removed Columns" = Table.RemoveColumns(join,{"Material", "Key1", "Key2"})

in
    #"Removed Columns"

Note: Depending on what you want to happen if there are specifications that are not matched at all; or if there are specifications that are matched multiple times, you may need to change the type of Join

Collectives™ on Stack Overflow

Power Query - Nested IF function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related