Log In
Register
Home
April 22, 2019
Offline
Hello world 😉
for me it's impossible but i'm sure someone can help me.
This is the excel formula:
| =DATE(IF(2000+MID(B6,7,2)>YEAR(TODAY()),MID(B6,7,2),2000+MID(B6,7,2)),FIND(MID( B6,9,1),"ABCDEHLMPRST"),MOD(MID(B6,10,2),40)) |
iI try this
"date(if(2000+Text.Middle([B6,7,2)>YEAR(TODAY()),Text.Middle(B6,7,2),2000+Text.Middle(B6,7,2)),FIND(Text.Middle( B6,9,1),"ABCDEHLMPRST"),MOD(Text.Middle(B6,10,2),40))
but it does not work 
July 16, 2010
Offline
Hi Stefano,
You can't reference cells in Power Query, so this:
Text.Middle(B6,7,2) will not be recognised, unless you happen to have a column called 'B6'.
Power Query also doesn't recognise the YEAR, TODAY, MOD and FIND functions. These are Excel functions. You need to find the Power Query equivalents.
That said, please upload a sample Excel file that references data in the workbook i.e. don't get data from an external file/folder/database. Show us the 'before' view and the desired result. From there we can help you with a Power Query solution.
Mynda
April 22, 2019
Offline
Thanks a lot Mynda 
you will find attached the worksheet .
Stefano
July 16, 2010
Offline
Hi Stefano,
Please see file attached. There are quite a few steps, but it makes no difference to Power Query's efficiency if it's not squashed into one single formula. That is the beauty of the query editor 🙂
Mynda
April 22, 2019
Offline
April 22, 2019
Offline
Hi Mynda,
thanks for your "wise solution" but I don't understand the meaning of one step...
this is the step
#"Inserted Modulo" = Table.AddColumn(#"Added Custom1", "Day", each Number.Mod([#"Column1(before).2.2.2.1"], 40), type number),
Or better I need the following operation :
"if the value of the day is greater than 40 reduce it by 40"....but how is applied this in logical steps with the "module"???
July 16, 2010
Offline
Hi Stefano,
In your Excel formula you had MOD(Text.Middle(B6,10,2),40)
The Modulo step in Power Query replicates this calculation in your Excel formula. i.e. Number.Mod is the equivalent Power Query function to Excel's MOD function.
Mynda
1 Guest(s)
