How to tansform the following DAX formula in a Power Query Formula?

[Skype]

Hello,

this does relate to my earlier post and might be a solution to it: My question is how could I translate the following Dax formula to a PowerQuery Formula?

=iferror(Right(Left([Kennzeichen];Search("A";[Kennzeichen];1)+6);6);"")

I would jump all day if I find a solution for that

 

[ImkeF]

Hope you had a good lunch then

if Text.PositionOf([Kennzeichen],"a") >= 0 then Text.End(Text.Start([Kennzeichen],Text.PositionOf([Kennzeichen],"a")+7),6) else null

- Why 7 and not 6?: Power Query starts to count at 0, so the first position will be returned as 0!
- If a is not found, -1 will be returned, so different case check then
- Power Query is case sensitive!!

 

[Skype]

Thanks a lot Still I could not manage to do this. It seems to be more complicated than just inserting a DAX formula. Attached I have a screenshot of my result. The column where I want to have my result is the very right column.

Greeting from Wiesbaden to Berlin.
Very useful blog bzw-

https://www.dropbox.com/s/7p6htvxf95hkkrb/powerquery.png

 

[billszysz]

Hi Skype
You have to change "a" to "A" in ImkeF formula (as ImkeF said, PQ is case sensitive)
Alternatively you can use this one below (or you can write many others)

try Text.Start(Text.Split([Kennzeichen], "A"){1}, 6) otherwise null

Regards

 

[ImkeF]

Bill's returns nearly the same than mine

Have a look at the file (last line): LinkToFile

 

[billszysz]

Hi Imke
You are right, of course but one little note about my solution.
This is only for data set presented at the picture by skype. This is not universal solution so the result for different data set can be improper.

Regards and sq

 

[Skype]

Amazing. That works. Thank you so much guys.

May I just ask you another question? How can I save this query so that sb just has to import the raw files and everything is done automatically? or do I always have to insert the formulas to perform the query?

 

[Skype]

Amazing. That works. Thank you so much guys.

May I just ask you another question? How can I save this query so that sb just has to import the raw files and everything is done automatically? or do I always have to insert the formulas to perform the query?

Ok I got the second question as well now