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
18
Medium Priority
?
363 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
[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
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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…

704 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