Parsing out text in a column in multiple columns

[Anonymous]

I'm trying to parsing out an Input Error message that has 3 differet values into 3 seperate columns.

Here's the original data:

Order Number	Error Description
123455	Document #1 ---INPUT ERROR--- Error: Mandatory Data Missing Element: Routing - Discarded ---INPUT ERROR--- Error: Mandatory Data Missing Element: Location is missing ---INPUT ERROR--- Error: Mandatory Data Missing Element: Volume missing

Ideal output:

Order Number	Error Output 1	Error Output 2	Error Output 3
123455	Error: Mandatory Data Missing Element: Routing - Discarded	Error: Mandatory Data Missing Element: Location is missing	Error: Mandatory Data Missing Element: Volume missing

[Anonymous · Accepted solution]

Hi @Anonymous ,

It's very simple, here's the solution.

1.Right-click in the Error Description column and select Split Column By Delimiter.

2.Select custom, enter "---INPUT ERROR---".

3.After completing the split, you only need to delete the unnecessary columns and rename the column names.

Check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much for the help. I'm thinking with this solution, I'll need to pivot the data set to make reporting a little easier. 

[lbendlin]

@Anonymous  I don't think that output format will do you any good.  I would go with something like this instead

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1VdJRcslPLs1NzStRUDZUitWJVgIK6erqevoFhIYouAYF+QcBeTAJ16Ki/CIrBd/EvJTEkvyiSgWXxJJEBd/M4uLMvHS4opxUkHlWCkH5pSVAcQVdBZfM4uTEopTUFCrb4JOfnFiSmZ+nkFmskIuqhko2hOXnAEMHZriCUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, #"Error Description" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Order Number"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Order Number"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Error Description] <> "---INPUT ERROR---")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Index] <> 0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Index2", each Number.RoundUp([Index]/2)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Order Number", "Error Description", "Index2"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Custom", each Text.Split([Error Description],":"){0}),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Custom]), "Custom", "Error Description")
in
    #"Pivoted Column"

[Anonymous]

How to show the data (Table) in the front end for PowerBI?

[lbendlin]

Not sure what you mean.  Do you have trouble applying my Power Query sample? How to use this code: Create a new Blank Query, then click on "Advanced Editor", and then replace the code in the window with the code provided here. Then click "Done".

[Anonymous]

Sorry, that was my bad...I didn't switch the source out. Thank you again for the help...this was exactly what I was looking for...