lasse0hlsen
New Member
- Joined
- Feb 1, 2020
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
Hi guys,
I am totally new to Power BI, the Power Query Editor and most of all the M language. I wanna try to import data from different Excel documents using the Power Query Editor and then merge different columns from these separate documents using the joins function. Finally, I plan to add another custom column to this merged table with a formula that refers to a sheet in another Excel document. My main difficulty for now is that I am not able to "translate" the following formula into the M language:
=IFNA(VLOOKUP(A2,'Implementing Organisations'!A2:B6, 2,FALSE), "unassignable")
Under the following link you can find a dummy Excel document, which basically shows what I am trying to do with the custom column function in the Power Query Editor (once I have managed to merge the two columns from the other Excel docs): Excel formula to be translated into M language.xlsx. You can see that the Excel workbook contains two different worksheets: The first one is titled "Sample data" and it contains the above-mentioned formula in column B. The formula compares the spelling of the organisations in column A with a pre-defined list of deviating and obligatory spellings in the worksheet "Implementing Organisations" - if the name of the organisation is incomplete the formula will automatically complete/correct the spelling of the name (provided that the correct name of the organisations is included in the list in the "Implementing Organisations" worksheet). Now here's my actual question: Could someone of you please explain to me how I could add a custom column with the this formula into my table in the Power Query Editor (see screenshot below)? I believe that my main difficulties are that in the M language there does not exist a function such as the IFNA function and that the formula refers to another Excel worksheet ("Implementing Organisations"), which is not part of the current query that I have created...
I look forward to your answers and want to thank you in advance for your help!
Best,
lasse0hlsen
I am totally new to Power BI, the Power Query Editor and most of all the M language. I wanna try to import data from different Excel documents using the Power Query Editor and then merge different columns from these separate documents using the joins function. Finally, I plan to add another custom column to this merged table with a formula that refers to a sheet in another Excel document. My main difficulty for now is that I am not able to "translate" the following formula into the M language:
=IFNA(VLOOKUP(A2,'Implementing Organisations'!A2:B6, 2,FALSE), "unassignable")
Under the following link you can find a dummy Excel document, which basically shows what I am trying to do with the custom column function in the Power Query Editor (once I have managed to merge the two columns from the other Excel docs): Excel formula to be translated into M language.xlsx. You can see that the Excel workbook contains two different worksheets: The first one is titled "Sample data" and it contains the above-mentioned formula in column B. The formula compares the spelling of the organisations in column A with a pre-defined list of deviating and obligatory spellings in the worksheet "Implementing Organisations" - if the name of the organisation is incomplete the formula will automatically complete/correct the spelling of the name (provided that the correct name of the organisations is included in the list in the "Implementing Organisations" worksheet). Now here's my actual question: Could someone of you please explain to me how I could add a custom column with the this formula into my table in the Power Query Editor (see screenshot below)? I believe that my main difficulties are that in the M language there does not exist a function such as the IFNA function and that the formula refers to another Excel worksheet ("Implementing Organisations"), which is not part of the current query that I have created...
I look forward to your answers and want to thank you in advance for your help!
Best,
lasse0hlsen
?
