cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Conditional Left Parse

I am new to Power BI and need some help. I need to parse the left part of the column before the (-) but there are several fields where there is no dash. In these cases, I want it to parse the left part of the column before the (.) and if there is neither, I want it to give me the entire field. Any suggestions on how I can accomplish that?

 

Sample Data:

 

Capture.PNG

 

 

1 ACCEPTED SOLUTION
Community Champion
Community Champion

You didn't mention if you want the result in the same column or in a new column.

 

The query below includes both alternatives.

 

let
    Source = #table(type table[PART_ID = text],{{"12-3983.98u"},{"897.yfdwhv"},{"jhjkkjygw"}}),

    Added = Table.AddColumn(Source, "ParsedPart", each if Text.Contains([PART_ID],"-") then Text.BeforeDelimiter([PART_ID],"-") else Text.BeforeDelimiter([PART_ID],".")),

    Transformed = Table.TransformColumns(Added,{{"PART_ID", each if Text.Contains(_,"-") then Text.BeforeDelimiter(_,"-") else Text.BeforeDelimiter(_,".")}})
in
    Transformed

 

This is how step "Added" looks like in the Query Editor with the popup for adding a custom column:

 

Conditional parse.png

Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
Community Champion
Community Champion

You didn't mention if you want the result in the same column or in a new column.

 

The query below includes both alternatives.

 

let
    Source = #table(type table[PART_ID = text],{{"12-3983.98u"},{"897.yfdwhv"},{"jhjkkjygw"}}),

    Added = Table.AddColumn(Source, "ParsedPart", each if Text.Contains([PART_ID],"-") then Text.BeforeDelimiter([PART_ID],"-") else Text.BeforeDelimiter([PART_ID],".")),

    Transformed = Table.TransformColumns(Added,{{"PART_ID", each if Text.Contains(_,"-") then Text.BeforeDelimiter(_,"-") else Text.BeforeDelimiter(_,".")}})
in
    Transformed

 

This is how step "Added" looks like in the Query Editor with the popup for adding a custom column:

 

Conditional parse.png

Specializing in Power Query Formula Language (M)

View solution in original post

This is perfect! Thank you so much. I wanted them in the same column and the second options works great. 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors