Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kevinbala
New Member

How to Extract 3 columnns from a column

Hi All,

 

I need some help with the data im working on, I'm trying to breakdown a column into three new columns on PowerBI

 

4437e54f-4fbd-4ca1-8c09-21e243a24d82.png

Conditions

  1. if the characters starts with a Letter, it moves to the Left, else leave it blank
  2. If the character ends with a W, then move it to the Right
  3. extract the numbers in between
  4. get rid of the '%'

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrEwC1eK1YlWcjQxhdAWFhABCwtVqIApVIWZGZg2N4DwLS1VoQohlCVEGGJiLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Default Header" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Actual%", each Text.Select([Default Header],{"0".."9"}), type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Left", each if List.Contains({"A".."Z"},Text.Start([Default Header],1)) then Text.Start([Default Header],1) else null, type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Right", each if Text.EndsWith([Default Header],"W") then "W" else null, type text)
in
    #"Added Custom2"

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrEwC1eK1YlWcjQxhdAWFhABCwtVqIApVIWZGZg2N4DwLS1VoQohlCVEGGJiLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Default Header" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Actual%", each Text.Select([Default Header],{"0".."9"}), type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Left", each if List.Contains({"A".."Z"},Text.Start([Default Header],1)) then Text.Start([Default Header],1) else null, type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Right", each if Text.EndsWith([Default Header],"W") then "W" else null, type text)
in
    #"Added Custom2"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors