Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2015-02-22
Medium Priority
363 Views
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
Question by:Jorgen
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 6
• 2

LVL 24

Expert Comment

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/

0

LVL 4

Author Comment

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

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

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

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

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

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

ID: 40627473
Error Message: Token eof expected
0

LVL 85

Expert Comment

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

LVL 4

Author Comment

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

ID: 40627882
Correct (with an = at the start of course)
0

LVL 4

Author Comment

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),
#"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
``````
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

ID: 40628212
Which version of Excel are you using?
0

LVL 4

Author Comment

ID: 40628216
2013 64 bit if that is an issue
0

LVL 85

Accepted Solution

Rory Archibald earned 2000 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),
#"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
``````

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),
#"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
``````
0

LVL 4

Author Comment

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

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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month6 days, 10 hours left to enroll