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