Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, this is my first post here.
I am trying to create a calculated column that extracts the first two letters of a postcode or first letter of a postcode if the second digit is a number.
Power query however stores the entire thing as text and although value works in excel it does not work in power query.
This is the formula I have tried:
=LEFT(December_Entry[Postcode],1)&IF(ISNUMBER(VALUE(RIGHT(LEFT(December_Entry[Postcode],2),1),"",RIGHT(LEFT(December_Entry[Postcode],2),1))
However this always gives me error because VALUE(RIGHT(LEFT(December_Entry[Postcode],2),1) returns an error since the column contains part text and numbers. Even if I create a new column using RIGHT(LEFT(December_Entry[Postcode],2),1) and then take the VALUE of this columns I still get an error.
In my desperation I tried using this:
=IF(RIGHT(LEFT(December_Entry[Postcode],2),1)=OR("9",OR(OR(OR("3","4"),OR("1","2")),OR(OR("5","6"),OR("7","8")))),"",RIGHT(LEFT(December_Entry[Postcode],2),1))
But this does not work either. Can anyone help?
Many thanks in advance
Solved! Go to Solution.
I have fixed the error, this is the calculated column I should be using:
=LEFT(December_Entry[Postcode],1)&IF(RIGHT(LEFT(December_Entry[Postcode],2),1)="0" || RIGHT(LEFT(December_Entry[Postcode],2),1)="1" || RIGHT(LEFT(December_Entry[Postcode],2),1)="2" || RIGHT(LEFT(December_Entry[Postcode],2),1)="3" || RIGHT(LEFT(December_Entry[Postcode],2),1)="4" || RIGHT(LEFT(December_Entry[Postcode],2),1)="5" || RIGHT(LEFT(December_Entry[Postcode],2),1)="6"|| RIGHT(LEFT(December_Entry[Postcode],2),1)="7" || RIGHT(LEFT(December_Entry[Postcode],2),1)="8" || RIGHT(LEFT(December_Entry[Postcode],2),1)="9", "", RIGHT(LEFT(December_Entry[Postcode],2),1))
Hi @JohnSonn
try a column
Column =
var _secondSymbol = MID(December_Entry[Postcode];2;1)
return
if (NOT(ISERROR(VALUE(_secondSymbol)));BLANK();_secondSymbol)
or (worse)
Column =
var _secondSymbol = MID(December_Entry[Postcode];2;1)
return
if (_secondSymbol IN {"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};BLANK();_secondSymbol)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi there az38,
I have tried implementing the code that you gave me into the 'Custom Column' function but I am getting various errors.
Can you please tell me where I should enter these lines of code?
I have fixed the error, this is the calculated column I should be using:
=LEFT(December_Entry[Postcode],1)&IF(RIGHT(LEFT(December_Entry[Postcode],2),1)="0" || RIGHT(LEFT(December_Entry[Postcode],2),1)="1" || RIGHT(LEFT(December_Entry[Postcode],2),1)="2" || RIGHT(LEFT(December_Entry[Postcode],2),1)="3" || RIGHT(LEFT(December_Entry[Postcode],2),1)="4" || RIGHT(LEFT(December_Entry[Postcode],2),1)="5" || RIGHT(LEFT(December_Entry[Postcode],2),1)="6"|| RIGHT(LEFT(December_Entry[Postcode],2),1)="7" || RIGHT(LEFT(December_Entry[Postcode],2),1)="8" || RIGHT(LEFT(December_Entry[Postcode],2),1)="9", "", RIGHT(LEFT(December_Entry[Postcode],2),1))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |