Convert big formula from excel to m for power query|Power Query|Excel Forum|My Online Training Hub

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.myonlinetraininghu.....ch-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 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