

April 22, 2019

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

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

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"???
1 Guest(s)
