How can I transform this Excel formula in a table to a custom column in Power Query

Hi Experts
I have a formula in an Excel table, that looks like you can see below. I want to be able to find the same value in PowerQuery, but I am not experienced in adding columns in PowerQuery.

I have tried to copy the formula into the column Tool and convert the column names of the Excel table to the columns that are in the importdata in PowerQuery, and I have tried to make the if statement Work, but have had no luck.

=IF(AND([@Text]=1,LEN([@Placement])>13),20,IF(AND([@Text]=1,LEN([@Placement])>0),(VALUE((MID([@Placement],6,4))-VALUE(MID([@Placement],1,4)))/100)*VALUE(MID([@Placement],11,1)),0))

The content of the placement cell could be something like

"1600-1700=2," telling that between 16 and 17 there have been used two lanes summing up to 2 lanes (1 hour times 2 lanes)
"1700-1900=1," telling that between 17 and 19 there have been used one lane, which should sum up to 2 lanes (2 hours times 1 lane)
LVL 4
JorgenAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
To correct your version you just need to test if Text = "1" rather than = 1

let
    Source = Csv.Document(File.Contents("C:\Users\JWR\Documents\DGI-Byen\RevisionExport2014.csv"),null,";",null,1252),
    #"First Row as Header" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"ID", Int64.Type}, {"Date", type date}, {"Time", type time}, {"Type", Int64.Type}, {"Status", Int64.Type}, {"CashDrawerId", Int64.Type}, {"ReceiptNo", Int64.Type}, {"Count", Int64.Type}, {"PLU", Int64.Type}, {"ArtLongId", Int64.Type}, {"Location", Int64.Type}, {"Text", type text}, {"Info", type text}, {"Details", type text}, {"APrice", Int64.Type}, {"Price", Int64.Type}, {"Amount", Int64.Type}, {"Customer", Int64.Type}, {"Table", type text}, {"Chair", type text}, {"Payment", type text}, {"ResvId", Int64.Type}, {"EventId", Int64.Type}, {"ResvDate", type date}, {"Placement", type text}, {"User", Int64.Type}, {"Authorized", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = 0)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Antal Baner", each if [Text] = "1" then
 if Text.Length([Placement]) > 13 then
  20
  else
  if Text.Length([Placement]) > 0 then
  (Number.FromText(Text.Range([Placement], 5, 4)) - Number.FromText(Text.Range([Placement], 0, 4))) / 100 * Number.FromText(Text.Range([Placement], 10, 1))
 else
 0 else
 0)
in
    #"Added Custom"

Open in new window


Adding the error handler I was using, it would be:
let
    Source = Csv.Document(File.Contents("C:\Users\JWR\Documents\DGI-Byen\RevisionExport2014.csv"),null,";",null,1252),
    #"First Row as Header" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"ID", Int64.Type}, {"Date", type date}, {"Time", type time}, {"Type", Int64.Type}, {"Status", Int64.Type}, {"CashDrawerId", Int64.Type}, {"ReceiptNo", Int64.Type}, {"Count", Int64.Type}, {"PLU", Int64.Type}, {"ArtLongId", Int64.Type}, {"Location", Int64.Type}, {"Text", type text}, {"Info", type text}, {"Details", type text}, {"APrice", Int64.Type}, {"Price", Int64.Type}, {"Amount", Int64.Type}, {"Customer", Int64.Type}, {"Table", type text}, {"Chair", type text}, {"Payment", type text}, {"ResvId", Int64.Type}, {"EventId", Int64.Type}, {"ResvDate", type date}, {"Placement", type text}, {"User", Int64.Type}, {"Authorized", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = 0)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Antal Baner", each try if [Text] = "1" then
 if Text.Length([Placement]) > 13 then
  20
  else
  if Text.Length([Placement]) > 0 then
  (Number.FromText(Text.Range([Placement], 5, 4)) - Number.FromText(Text.Range([Placement], 0, 4))) / 100 * Number.FromText(Text.Range([Placement], 10, 1))
 else
 0 else
 0 otherwise 0)
in
    #"Added Custom"

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It's hard to answer this question, because I'm not clear what you are trying to do - what answer you want.

However,

Don't use VALUE - use ToText
Don't use MID - use Range
Don't use IF(AND( - use IF THEN ELSE IF - see http://www.excelguru.ca/blog/2014/09/03/multi-condition-logic-in-power-query/

See https://support.office.com/en-sg/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819 for more information on the functions..
0
 
JorgenAuthor Commented:
Hi Phillip,

I will take a look at the articles.

I found a reference in Chris Webb's book on power Query that explained about the IF THEN and ELSE IF yesterday evening, but can that be used with the AND statement included? The articles you Refer to might give me the answer, but if you have a quick answer, it will be very welcome.

regards

Jørgen
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I don't have a quick answer, but given that I have done a search and not found it, I would say "no".
0
 
Rory ArchibaldCommented:
You basically want something like this I think:

try if [Text] = 1 then
if Text.Length([Placement]) > 13 then
 20
 else
 if Text.Length([Placement]) > 0 then
 (Number.FromText(Text.Range([Placement], 5, 4)) - Number.FromText(Text.Range([Placement], 0, 4))) / 100 * Number.FromText(Text.Range([Placement], 10, 1))
else
0 else
0 otherwise 0)
0
 
JorgenAuthor Commented:
I will take a look at the articles, as well as a couple of other references that I did find. I need the solution to match, if I should give the solution to my coleagues for update afterwards.
0
 
JorgenAuthor Commented:
Hi Rory,

that looks like the way Chris Webb did build up, the solution in the power Query book, so I will have a go for it as soon as I am back on my own computer.

regards

Jørgen
0
 
JorgenAuthor Commented:
@ Rory

It seems like a get an error on the otherwise command, but that is the only error I can see. Do you have any suggestions
for that.

regards

Jørgen
0
 
JorgenAuthor Commented:
Error Message: Token eof expected
0
 
Rory ArchibaldCommented:
Sorry - you should remove the final parenthesis from the formula I posted - I snipped it out of the final M code for the whole column and missed that bit.
0
 
JorgenAuthor Commented:
So it should be:

if [Text] = 1 then
 if Text.Length([Placement]) > 13 then
  20
  else
  if Text.Length([Placement]) > 0 then
  (Number.FromText(Text.Range([Placement], 5, 4)) - Number.FromText(Text.Range([Placement], 0, 4))) / 100 * Number.FromText(Text.Range([Placement], 10, 1))
 else
 0 else
 0 otherwise 0

????
0
 
Rory ArchibaldCommented:
Correct (with an = at the start of course)
0
 
JorgenAuthor Commented:
Hi Rory,

I tried to do that, but still got an error, and only if the code is like below it is accepted
let
    Source = Csv.Document(File.Contents("C:\Users\JWR\Documents\DGI-Byen\RevisionExport2014.csv"),null,";",null,1252),
    #"First Row as Header" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"ID", Int64.Type}, {"Date", type date}, {"Time", type time}, {"Type", Int64.Type}, {"Status", Int64.Type}, {"CashDrawerId", Int64.Type}, {"ReceiptNo", Int64.Type}, {"Count", Int64.Type}, {"PLU", Int64.Type}, {"ArtLongId", Int64.Type}, {"Location", Int64.Type}, {"Text", type text}, {"Info", type text}, {"Details", type text}, {"APrice", Int64.Type}, {"Price", Int64.Type}, {"Amount", Int64.Type}, {"Customer", Int64.Type}, {"Table", type text}, {"Chair", type text}, {"Payment", type text}, {"ResvId", Int64.Type}, {"EventId", Int64.Type}, {"ResvDate", type date}, {"Placement", type text}, {"User", Int64.Type}, {"Authorized", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = 0)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Antal Baner", each if [Text] = 1 then
 if Text.Length([Placement]) > 13 then
  20
  else
  if Text.Length([Placement]) > 0 then
  (Number.FromText(Text.Range([Placement], 5, 4)) - Number.FromText(Text.Range([Placement], 0, 4))) / 100 * Number.FromText(Text.Range([Placement], 10, 1))
 else
 0 else
 0)
in
    #"Added Custom"

Open in new window

and my machine keeps telling me, that the otherwise command is wrong.

But if I use this code I get 0 all the Places I Expected to get a number

I have attached the example file, so you can see how the structure of my data is , and see if you get the same error.
RevisionExport2014-Test.csv
0
 
Rory ArchibaldCommented:
Which version of Excel are you using?
0
 
JorgenAuthor Commented:
2013 64 bit if that is an issue
0
 
JorgenAuthor Commented:
Hi Rory
That works great.
Even with the otherwise part included.
The change of 1 to "1" was that due to being a textfield in the CSV file, or can you explain why it needs to be "1"

regards

Jørgen
0
 
Rory ArchibaldCommented:
That's correct. Your field contains a mix of numbers and text so is treated as Text by PQ.
0
 
JorgenAuthor Commented:
M code is a totally different ballgame ( at least for me) and it is great to find a helping and Qualified hand like Rorya,
0
All Courses

From novice to tech pro — start learning today.