cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
L70F Frequent Visitor
Frequent Visitor

How to apply Index/Match/If for array function in Power Query?

Hi, I am new with Power Query and need your help in my project.  

 

In Excel I have solved this task with Index/Match/If arrow function but I have big difficulty to find some good solution in Power Query.

I have a reference table with the columns: “Com.group”; “Qty Interval”; “Recommended_Qty”

And a workbook with columns: “ItemNo”; “Com.group”; “Annual Demand”.

I need:

For each ItemNo find the closest value below or equal "AnnualDemand" value within "Qty Interval" for right "Com.group". Therefore, pick the value from "Recommended_Qty".

 

Expected Result:

The workbook should be completed with value “Qty Interval” and “Recommended Qty” for each ItemNo from Reference table.

 

Reference table
Com.groupQty intervalRecommended Qty
301; 2; 5; 10
3105; 10; 20; 30
33020; 30; 50; 100
3100Brackets are not applicable! 
3500Brackets are not applicable! 
31000Brackets are not applicable! 
401; 5; 10; 20
4101; 5; 10; 20
43010; 20; 30; 50
410050; 100; 200; 500
4500Brackets are not applicable! 
41000Brackets are not applicable! 
501; 5; 10; 50
5101; 5; 10; 50
53020; 50; 100; 200
510050; 100; 200; 500
5500200; 500; 700; 1000
51000Brackets are not applicable! 

 

 Workbook Wished resultWished result
ITEM_NOCom.groupAnnual demandQty IntervalRecommended Qty
TE303315100Brackets are not applicable! 
AG1003833020; 30; 50; 100
112416101; 5; 10; 20
705412510050; 100; 200; 500
787530001000Brackets are not applicable! 
9995753020; 50; 100; 200

 

I really appreciate your soon suggestion for solution since I’m stuck with this for a couple of weeks now.

Thanks a lot.

 



2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: How to apply Index/Match/If for array function in Power Query?

Hi,

Try this in your power query advanced editor:

let
    Origen = Excel.CurrentWorkbook(){[Name="tWorkbook"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"ITEM_NO", type text}, {"Com.group", Int64.Type}, {"Annual demand", Int64.Type}}),
    #"Consultas combinadas" = Table.NestedJoin(#"Tipo cambiado",{"Com.group"},tReference,{"Com.group"},"tReference",JoinKind.LeftOuter),
    #"Se expandió tReference" = Table.ExpandTableColumn(#"Consultas combinadas", "tReference", {"Qty interval"}, {"Qty interval"}),
    #"Columna condicional agregada" = Table.AddColumn(#"Se expandió tReference", "Personalizado", each if [Qty interval] <= [Annual demand] then 1 else 0),
    #"Filas filtradas" = Table.SelectRows(#"Columna condicional agregada", each ([Personalizado] = 1)),
    #"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Personalizado"}),
    #"Filas agrupadas" = Table.Group(#"Columnas quitadas", {"ITEM_NO", "Com.group", "Annual demand"}, {{"Qty Interval", each List.Max([Qty interval]), type number}}),
    #"Consultas combinadas1" = Table.NestedJoin(#"Filas agrupadas",{"Com.group", "Qty Interval"},tReference,{"Com.group", "Qty interval"},"tReference",JoinKind.LeftOuter),
    #"Se expandió tReference1" = Table.ExpandTableColumn(#"Consultas combinadas1", "tReference", {"Recommended Qty"}, {"Recommended Qty"})
in
    #"Se expandió tReference1"

AdvancedEditor.png

 

 

Tell us if works for you.

 

Anonymous
Not applicable

Re: How to apply Index/Match/If for array function in Power Query?

Hi L,

I can to replicate the issue. You must have one or more some cells empty that is interpreted by power query as "null".

 

You have to delete each of their rows or fill each of them with their right information in your source or add other step with what power query should do with those "null" values.

 

Kind regards.

 

Replica1.pngReplica-Cause.png

10 REPLIES 10
Highlighted
Anonymous
Not applicable

Re: How to apply Index/Match/If for array function in Power Query?

Hi,

Try this in your power query advanced editor:

let
    Origen = Excel.CurrentWorkbook(){[Name="tWorkbook"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"ITEM_NO", type text}, {"Com.group", Int64.Type}, {"Annual demand", Int64.Type}}),
    #"Consultas combinadas" = Table.NestedJoin(#"Tipo cambiado",{"Com.group"},tReference,{"Com.group"},"tReference",JoinKind.LeftOuter),
    #"Se expandió tReference" = Table.ExpandTableColumn(#"Consultas combinadas", "tReference", {"Qty interval"}, {"Qty interval"}),
    #"Columna condicional agregada" = Table.AddColumn(#"Se expandió tReference", "Personalizado", each if [Qty interval] <= [Annual demand] then 1 else 0),
    #"Filas filtradas" = Table.SelectRows(#"Columna condicional agregada", each ([Personalizado] = 1)),
    #"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Personalizado"}),
    #"Filas agrupadas" = Table.Group(#"Columnas quitadas", {"ITEM_NO", "Com.group", "Annual demand"}, {{"Qty Interval", each List.Max([Qty interval]), type number}}),
    #"Consultas combinadas1" = Table.NestedJoin(#"Filas agrupadas",{"Com.group", "Qty Interval"},tReference,{"Com.group", "Qty interval"},"tReference",JoinKind.LeftOuter),
    #"Se expandió tReference1" = Table.ExpandTableColumn(#"Consultas combinadas1", "tReference", {"Recommended Qty"}, {"Recommended Qty"})
in
    #"Se expandió tReference1"

AdvancedEditor.png

 

 

Tell us if works for you.

 

Super User
Super User

Re: How to apply Index/Match/If for array function in Power Query?

Hi,

 

Try these calculated column formulas in Table2

 

Qty interval = CALCULATE(Max(Table1[Qty interval]),FILTER(Table1,Table1[Com.group]=EARLIER(Table2[Com.group])&&Table1[Qty interval]<=EARLIER(Table2[Annual demand])))

 

Recommended qty = LOOKUPVALUE(Table1[Recommended Qty],Table1[Com.group],Table2[Com.group],Table1[Qty interval],[Qty interval])

 

Hope this helps.

L70F Frequent Visitor
Frequent Visitor

Re: How to apply Index/Match/If for array function in Power Query?

Thank you Miltinho Smiley Happy

You solution works well. I am happy. Smiley Happy

L70F Frequent Visitor
Frequent Visitor

Re: How to apply Index/Match/If for array function in Power Query?

Hi Ashish, thank you for your suggestion. Unfortunately, I can't get it working. When I copy your formula in the Custom column in Query for Table2 I get the wrong message. What am I doing wrong?

Add Custom column in PQ.jpg

 

Error message1.jpgError message2.jpg

 

 

L70F Frequent Visitor
Frequent Visitor

Re: How to apply Index/Match/If for array function in Power Query?

Hi Miltinho,

 I have tried your solution with given example and it works well even when I have a value of “Annual demand”=0.

 

 test.jpg 

But when I try to apply the same to my project I suddenly stuck on the step “Filas Agrupades” . Please, see the picture below.

 Problem.jpg

Do you have any idea what might be the reason of that?

 

-Different type of "Qty interval", Int64.Type in table tReference, but type number in the function List.Max?

-Might Power Query doesn’t recognize the function of List.Max?

 

 Thanks for help

 

Super User
Super User

Re: How to apply Index/Match/If for array function in Power Query?

Hi,

 

You are writing my formula at the wrong place.  Mine is a calculated formula solution - not an M solution.

Anonymous
Not applicable

Re: How to apply Index/Match/If for array function in Power Query?

Hi L,

I can to replicate the issue. You must have one or more some cells empty that is interpreted by power query as "null".

 

You have to delete each of their rows or fill each of them with their right information in your source or add other step with what power query should do with those "null" values.

 

Kind regards.

 

Replica1.pngReplica-Cause.png

L70F Frequent Visitor
Frequent Visitor

Re: How to apply Index/Match/If for array function in Power Query?

Hi Ashish, 

where can I found the right place to insert your culculation formula? I have done it by insert "Custom column" and then copy in it your formula. But it didn't work. I tried also with "Invoke custom function", but come nowhere with it. Pehaps you can show some screen shots so I can learn me. 

Thanks a lot. 

Best regards 

Larissa

L70F Frequent Visitor
Frequent Visitor

Re: How to apply Index/Match/If for array function in Power Query?

Thanks Miltinho, I could find the fault and now all work well. Smiley Happy

Best regards

Larissa

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 53 members 1,027 guests
Please welcome our newest community members: