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:   ...
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
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