Extracting Text in Power Query

[Anonymous]

I have following table column:

I want to get a new column with text after delimiter for each line in each row if delimiter "-" exists else null.. Eg in the 1st row I should get Timesheet Report and Review.

I wrote the following query but it is giving me only 'Timesheet Report' but not Review

if [Project]="Free Time" and Text.Contains([#"Work Description"], "-") then Text.BetweenDelimiters([#"Work Description"],"-","#(lf)") else nulll

Any help is appreciated

Thanks in Advance

[Fowmy]

@Anonymous 

You need to first split your records into rows, using Line Feed 

Then, under Add Column tab, Extract and choose Text After Delimiter as follows

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY3BCsIwEAV/ZcnZBNQ/kIIXD1Lrqeaw2Eez0Cal2Rr8e0suHgdmmL43R0c38BoljmQ7mZEDoNRiSavSK54cXaY02hYfQTH+UJOGv3SfONasStdNhh1sB56pkfzecpYUqYgGeoQC5V08O/o/nsvACuP9Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"c Work Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"c Work Description", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"c Work Description", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "c Work Description"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"c Work Description", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type1", "Text Before Delimiter", each Text.AfterDelimiter([c Work Description], "-"), type text)
in
    #"Inserted Text Before Delimiter"

[Anonymous]

Hi @Fowmy 

Is it possible to extract without splitting into rows because I want the content of that particular row together since each work description belongs to a member?

[Fowmy]

@Anonymous 

When you split into rows, all other columns including the member column will be repeated for each row. You group them as you need. This is a good approach. Check this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc1BCsIwFATQq3yybgPqCSwFNwrS1lXNItqh+dAkJUkN3t5SF10OzJvpe3GDxQuBzqIQB0lX6ODYjVR2bBENkKjB7EOipztKqiY/lg0+jCxUsfPqz2v9pfuk3TaxgcvCwxrKDtpSzfG9xMjeUeZkqDUZSa/Fk6T97zEPOkEo9QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Memeber = _t, #"c Work Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"c Work Description", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"c Work Description", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "c Work Description"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"c Work Description", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type1", "Text Before Delimiter", each Text.AfterDelimiter([c Work Description], "-"), type text)
in
    #"Inserted Text Before Delimiter"

How do you want the new column ?. You show in different rows to concatenate them

[Anonymous]

@Fowmy 

I dont want multiple records for the same member 

In my desired output the row with Member A should have Timesheet Report, Review against it in a single row

[Fowmy]

@Anonymous 

You try this :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc1BCsIwFATQq3yybgPqCSwFNwrS1lXNItqh+dAkJUkN3t5SF10OzJvpe3GDxQuBzqIQB0lX6ODYjVR2bBENkKjB7EOipztKqiY/lg0+jCxUsfPqz2v9pfuk3TaxgcvCwxrKDtpSzfG9xMjeUeZkqDUZSa/Fk6T97zEPOkEo9QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Memeber = _t, #"c Work Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"c Work Description", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"c Work Description", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "c Work Description"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"c Work Description", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type1", "Text Before Delimiter", each Text.AfterDelimiter([c Work Description], "-"), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Text Before Delimiter", {"Memeber"}, {{"Result", each Text.Combine([Text Before Delimiter]," "), type text}})
in
    #"Grouped Rows"

[Anonymous]

@Fowmy I have a date and Free Time column too in this table as shown in the image. So if I group by Team Member for each day using Table.Group function then it will return a table. I tried your solution but didn't work in my case

[v-jingzhang]

Hi @Anonymous 

You just need to modify the last step of @Fowmy 's solution. You can group by Team member, Date and Project columns at the same time. Do you want below result?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY6xDoIwGIRf5U9nSqQOzhKiCyYGcUKGKhf4EyikLRLf3oqDq9t3ucuXqyqhNiqRiZJqJyJxsACVPCDwCQPusLQPnMSUQ1vDpiX56V0HeCowjdbTzaiY0n5sZYEnYxF19Ic3/Xoz/aJzr83qXk3HmZsQZAk9UMbuMTvHo6GFfUeXboHXYbiN6XfkOjXaQ9T1Gw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Project = _t, Memeber = _t, #"c Work Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"c Work Description", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"c Work Description", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "c Work Description"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"c Work Description", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type1", "Text Before Delimiter", each Text.AfterDelimiter([c Work Description], "-"), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Text Before Delimiter", {"Memeber","Date","Project"}, {{"Result", each Text.Combine([Text Before Delimiter],"#(lf)"), type text}})
in
    #"Grouped Rows"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[mahoneypat]

Please try this instead.

Text.AfterDelimiter([Work Description], "- ")

Pat

[Anonymous]

@mahoneypat 

I tried that too but it only extracts text for single line. Eg. From 1st row Timesheet Report and Review should be extracted but Text.AfterDelimiter([Work Description], "- ") extracts 'Timesheet Report Blog-Review' since it considers Blog-Review is also after the first delimeter '-'