Appending two rows in a single row then sort it based on the values of the new row

[Anonymous]

Hello Community,

I have come across a challange that would be appreciated to have your help:

I have a table as shown below which has several rows. some of the rows as you caan see are short to be shown in a single row but for some others as you can see they break down to two rows or more:

Now the question is since they are saved as text files before pushing them into PBI and there is no way to append several rows in to one row, how I can achieve this in PBI as  the image below:

Please note that the original files are all text format and I have to bring them as is to PBI.

after this I have to sort them based on the number of "-" meaning the ones with less "-" should be at first and ascending to show the ones with more "-".

Many thanks

[Anonymous · Accepted solution]

@Anonymous , this M code should do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk5MSdOFE0qxOuhCuonFyOJp5algEd3ilMQ0XbA4kKubmJZSDOOkQRUkorBQZZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t])
    ,Add_Index = Table.AddIndexColumn(Source, "Index", 1, 1)
    ,Add_PreviousIndex = Table.AddIndexColumn(Add_Index, "Previous_Index", 0, 1)
    ,Add_EndFlag = Table.AddColumn(Add_PreviousIndex, "End Flag", each 
        if Text.End([Column1], 1) = "-" 
            then true 
        else false
        ,type logical)
    ,Buffer = Table.Buffer(Add_EndFlag)
    ,Self_Join = Table.NestedJoin(
        Buffer, {"Index"}
        ,Buffer, {"Previous_Index"}
        ,"Buffer"
        ,JoinKind.LeftOuter)
    ,Expand_Buffer = Table.ExpandTableColumn(Self_Join, "Buffer"
        ,{"Column1"}
        ,{"Buffer.Column1"})
    ,Add_FinalColumn = Table.AddColumn(Expand_Buffer, "Final Column", each 
        if [End Flag] = true 
            then [Column1] & [Buffer.Column1] 
        else [Column1]
        ,type text)
    ,Keep = Table.SelectColumns(Add_FinalColumn
        ,{"Final Column"})
    ,Add_SortByDash = Table.AddColumn(Keep, "Sort by Dash", each 
        List.Count(
            Text.Split(
                [Final Column]
                ,"-"
            )
        ) - 1
        , Int64.Type)
in
    Add_SortByDash

Note that my Source = step is just a hardcoded text to test this.  I'll walk you through the code

Create 2 index columns...one that starts a 0 and the other that starts at 1.

Then we check if the row ends with a "-"...true or false

Buffer the table into memory (this will help with the self-join step coming next).

Join the table to itself on the two index columns.  This will make the "current" row join to the "next" row, and be able to access its columns in the expand step.

Then, check if the end flag is true...if it is then concatenate the 2 columns together.  If false, just return the single column.

Remove all of the extra, no longer needed columns.

Then, count the number of "-" that appear.  This works by first splitting the text into a list by the delimiter "-".  Then count the list.

But "123-456-789" split by "-" will return 3 items.  Subtracting 1 gives us 2 instances of "-" in the text.

Hope this helps!

~ChrisH 

http://www.precisiondax.com