Hi Stefano,

This should be done with merge in Power Query. i.e. set up a table that has your mapping of years and risk codes to tickets, then use merge to bring the ticket number into the table. See this tutorial: https://www.myonlinetraininghub.com/power-query-approximate-match-vlookup

This example only has one column to match, the order value, but in your query you'll need to add a column to extract the year and then match the year and risk code in the Merge dialog box by holding down CTRL as you select the matching columns.

I hope that points you in the right direction. If you get stuck, come back with your file again with the lookup table set up and we'll help you further.

Mynda

Hi Mynda,

thanks a lot for your advice but i don't understand you.

IN my table (worksheet big-formula) I have all the necessary values: date, risk and i can create the ticket as formula.

i.e row 2 : 

the year is <" today - 4 year "

and risk is "BASSA" then ticket =30

I' d like to convert this "excel formula" in "m formula" for PQ.

Is not possible to create a  Custom Column called "ticket" with the same values obtained with the " ticket formula".

Thk

Stefano

Hi Stefano,

I'm trying to advise you on the correct way to assign these attributes to your data. A nested 'if' is the most inefficient way to both create the solution and for Excel to calculate it.

You can re-write the formula if you want: how to write if statements in Power Query. I'm just advising you not to do it that way. You should instead create a lookup table and use Power Query Merge to perform the lookup.

Mynda

Hi Mynda

I'll follow your wise advise!

Stefano

Your example is very clear but it is difficult for me to apply it to my problem.

In addition to working with quarters and years, transforming them from text to date is not very clear to me.

I would not like to take advantage of your kind availability but you would be of great help if you could give me suggestions closer to my case.

I am attaching the two necessary worksheets.

Thanks 

Stefano

Hi Stefano,

Please see attached - the output of the query is on the Tables sheet in the blue table.

If you'd like to learn Power Query, please consider my Power Query course.

Mynda

Thanks a Lot, Mynda.