Solved

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

Posted on 2015-02-22
18
244 Views
Last Modified: 2015-02-24
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)
0
Comment
Question by:Jorgen
  • 10
  • 6
  • 2
18 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40625243
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
 
LVL 4

Author Comment

by:Jorgen
ID: 40625253
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40625254
I don't have a quick answer, but given that I have done a search and not found it, I would say "no".
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40625257
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
 
LVL 4

Author Comment

by:Jorgen
ID: 40625259
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
 
LVL 4

Author Comment

by:Jorgen
ID: 40625266
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
 
LVL 4

Author Comment

by:Jorgen
ID: 40627469
@ 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
 
LVL 4

Author Comment

by:Jorgen
ID: 40627473
Error Message: Token eof expected
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40627644
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Author Comment

by:Jorgen
ID: 40627863
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40627882
Correct (with an = at the start of course)
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40628122
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40628212
Which version of Excel are you using?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40628216
2013 64 bit if that is an issue
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40628364
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
 
LVL 4

Author Comment

by:Jorgen
ID: 40628505
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40628571
That's correct. Your field contains a mix of numbers and text so is treated as Text by PQ.
0
 
LVL 4

Author Closing Comment

by:Jorgen
ID: 40628639
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now