Power Query: Filter/Split values in rows from one column into multiple columns from a dynamic row

[databot_kd]

Hi community, 

Please can you assist we have a  list of organisation numbers in one column in a .txt file. 

This list contains all successful and unsuccesful organisation, example below:

Org Num
Successful
123456
789456
98697
Not Successful
7896563
56986358
59789

My requirement is to split the one column into two columns, Successful |  Not successful 

Successful	Not Successful
123456	7896563
789456	56986358
98697	59789

Or just return only the Successful list if thats easier.

Thanks in advance

[Omid_Motamedise]

Use this formula

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
S1 = Source[Org Num],
Custom1 = Table.PromoteHeaders(Table.FromRows(List.Zip(List.Split(S1,List.PositionOf(S1,"Not Successful")))))
in
Custom1

[Em1278]

Add Custom Column:

• Right-click on "Column1" and select "Add Column" -> "Custom Column."
• Name this new column "Status."
• In the formula bar, enter this formula: if Text.Contains([Column1], "Successful") then "Successful"
  else if Text.Contains([Column1], "Not Successful") then "Not Successful"
  else null 

• Add another custom column named "Org Num."
• In the formula bar, enter: Text.Combine(List.Select(Text.Split([Column1], " "), each Text.Length(_) > 0 and Text.IsNumber(_)), " ")

• Filter out rows where the "Status" column is null.
• Right-click on the "Status" column and select "Pivot Column."
• In the Pivot Column window:
  • Values Column: "Org Num"
  • Aggregation Function: "Don't Aggregate"

[dufoq3 · Accepted solution]

Hi @databot_kd, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5NTk4tLk4rzVFQitWJVjI0MjYxNQMzzS0sYUxLCzNLczALosovv0QBoROm2szUzBjMNjUDajA2tYBwLIFSSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Org Num" = _t]),
    Transformed = Table.FromColumns(List.Combine(List.Transform(Table.SplitAt(Source, List.PositionOf(Source[Org Num], "Not Successful")), Table.ToColumns))),
    PromotedHeaders = Table.PromoteHeaders(Transformed, [PromoteAllScalars=true]),
    FilteredRows = Table.SelectRows(PromotedHeaders, each ([Not Successful] <> null))
in
    FilteredRows

[Vidushi_Mangal]

Add Custom Column:

• Right-click on "Column1" and select "Add Column" -> "Custom Column."
• Name this new column "Status."
• In the formula bar, enter this formula: if Text.Contains([Column1], "Successful") then "Successful"
  else if Text.Contains([Column1], "Not Successful") then "Not Successful"
  else null 

• Add another custom column named "Org Num."
• In the formula bar, enter: Text.Combine(List.Select(Text.Split([Column1], " "), each Text.Length(_) > 0 and Text.IsNumber(_)), " ")

• Filter out rows where the "Status" column is null.
• Right-click on the "Status" column and select "Pivot Column."
• In the Pivot Column window:
  • Values Column: "Org Num"
  • Aggregation Function: "Don't Aggregate"

Give a thumsup if solution is up to mark