Forum Discussion

databot_kd's avatar
databot_kd
Helper III
2 years ago
Solved

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

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
1234567896563
78945656986358
9869759789

 

Or just return only the Successful list if thats easier.

 

Thanks in advance

Need Help
Show and Tell
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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
  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    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

  • Vidushi_Mangal's avatar
    Vidushi_Mangal
    Regular Visitor
    2 years ago

    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

  • Em1278's avatar
    Em1278
    New Member
    1 year ago

    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"