Split Column into Rows in Power BI

[Jacb]

Hi,
I am trying to ascertain the most efficient way to split the 'Details' column into multiple columns.  

Details	Transaction
• Client A	7.29
• A0021	7.29
• 992860	7.29
•Client B	150
•C0035	550
• 992860	150
• 984521	400
• Client C	250
• EC003	250
• 992860	250

I want the output to look something like this:

Customer	Code	Account	Transaction
Client A	A0021	992860	7.29
Client B	C0035	992860	150
Client B	C0035	984521	400
Client C	EC003	992860	250

I've tried to use the UI to split the Details Column by using delimiter and special character line feed •#(lf) but it splits out the columns and only displays null values. 
I've tried to add an index column to the data and then created a custom field called, 'Column' that looks at each Number.mod([index], 3})) however, when I apply this the new field is showing the correct sequence for those that only have one account associated to them i.e. Column would contain values  0,1,2 .  However, when it encounters an instance with more than one account the sequence values change and it thinks its a new identity. and displays this as 0,1,2,0.  Any ideas how I can easily rectify the above? 

Thanks

[AlienSx]

let
    group = Table.Group(
        data, 
        "Details", 
        {"x", (x) => Table.AddColumn(Table.Skip(x,2), "Code", (w) => x[Details]{1})}, 
        GroupKind.Local, 
        (s, c) => Number.From(Text.Contains(c, "Client"))
    ),
    expand = Table.ExpandTableColumn(group, "x", {"Code", "Details", "Transaction"}, {"Code", "Account", "Transaction"})
in
    expand

[Jacb]

Thanks for the solution. 
Where you have stated the Text.Contains "Client" - my actual data doesn't contain the word Client - it contains the actual client name but I can't disclose that here.   What would I change this statement to use instead?
Thanks

[watkinnc]

I would add two custom columns as

if Text.StartsWith([Client], "Client" then [Client] else null

Fill that column down, then Table.SelectRows using each [Client] <> [NewClientColumn].
Do the same thing with the Code column ( add column, fill down, select rows). Then just rearrange your columns--that's it.

--Nate

[Omid_Motamedise]

Use the following formula which is based on Table.Group

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUXBU0lEy1zOyVIrViVZyNDAwMkQWsLQ0sjAzQBaBanMCihmaGkCEDAyMTYF8UygfrgmmwNLCxBRsromBAbIhzkAhI6gaV5ApSHy4IWCBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Details = _t, Transaction = _t]),
#"Grouped Rows" = Table.Group(Source, "Details", {{"Count", each [a=_,b=Table.AddColumn(Table.FirstN(a,1),"Code", (x)=> Text.Combine(List.Skip(a[Details])))][b]}},0,(a,b)=>Number.From(Text.StartsWith(b,"Client"))),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Details"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Details", "Transaction", "Code"}, {"Details", "Transaction", "Code"})
in
#"Expanded Count"

[Jacb]

Thanks for the solution. 
Where you have stated the Text.StartsWith "Client" - my actual data doesn't contain the word Client - it contains the actual client name but I can't disclose that here.   What would I change this statement to use instead?
Thanks

[AntrikshSharma]

@Jacb Try this:

let
    Source = Table,
    GroupedRows = Table.Group (
            Source,
            { "Details" },
            {
                {
                    "Transform",
                    each
                        let
                            a = _,
                            b = Table.Skip ( a ),
                            c = b[Details]{0},
                            d = Table.TransformColumns (
                                    Table.Skip ( b ),
                                    { { "Details", each c & _ } }
                                ),
                            e = { List.Repeat ( { Table.FirstValue ( a ) }, Table.RowCount ( d ) ) }
                                & Table.ToColumns ( d ),
                            f = Table.FromColumns (
                                    e,
                                    type table [
                                        Customer Code = text,
                                        Account = text,
                                        Transaction = Currency.Type
                                    ]
                                )
                        in
                            f
                }
            },
            GroupKind.Local,
            ( x, y ) => Byte.From ( Text.StartsWith ( y[Details], "Client" ) )
        ),
    Combine = 
        Table.Combine ( GroupedRows[Transform] )
in
    Combine

PBIX attached below:

[Jacb]

Thanks for the solution. 
Where you have stated the Text.StartsWith "Client" - my actual data doesn't contain the word Client - it contains the actual client name but I can't disclose that here.   What would I change this statement to use instead?
Thanks

[AntrikshSharma · Accepted solution]

@Jacb In that case you can check if the Text doesn't contain Number or you can prepare a list of Client Name and check if the Text is in that list.

( x, y ) => Byte.From ( 
    List.Count ( 
        List.Intersect ( 
            { Text.ToList ( y[Details] ), 
            { "0".."9" } } 
        ) 
    ) = 0 
)

With Client Name List

( x, y ) => Byte.From ( List.Contains ( ClientNames, y[Details] ) )

Refer to Solution_v2 and Solution_v3 in the PBIX attached below.

[BA_Pete]

Hi @Jacb ,

I wouldn't say this is an 'easy' way to rectify your scenario, but it's 'a' way. It's not at all elegant, but should do the job:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WetSwSME5JzM1r0TBUUlHyVzPyFIpVgci7mhgYGSILmhpaWRhZoAmCjXBCShsaGoAFzUwMDYFCpkihBDaDZEFLUxMwTaZGCAEoWY6A4WNkNS6gkxFE4MbChaMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Details = _t, Transaction = _t]),
    addClient = Table.AddColumn(Source, "Client", each if Text.Contains([Details], "Client") then Text.Trim(Text.Select([Details], {"a".."z", " ", "A".."Z"})) else null),
    fillDownClient = Table.FillDown(addClient, {"Client"}),
    groupClient = Table.Group(fillDownClient, {"Client"}, {{"data", each _, type table [Details=nullable text, Transaction=nullable text, Client=text]}}),
    addNestedClientIndex = Table.TransformColumns(groupClient, {"data", each Table.AddIndexColumn(_, "ClientIndex", 1, 1)}),
    addNestedAcctStart = Table.TransformColumns(addNestedClientIndex, {"data", each Table.AddColumn(_, "acctStart", each if [ClientIndex] = 2 then Text.Select([Details], {"a".."z", "A".."Z", "0".."9"}) else null)}),
    addNestedAcctEnd = Table.TransformColumns(addNestedAcctStart, {"data", each Table.AddColumn(_, "acctEnd", each try Number.From(Text.Select([Details], {"a".."z", "A".."Z", "0".."9"})) otherwise null)}),
    fillDownAcctStart = Table.TransformColumns(addNestedAcctEnd, {"data", each Table.FillDown(_, {"acctStart"})}),
    filterNestedAcctEnd = Table.TransformColumns(fillDownAcctStart, {"data", each Table.SelectRows(_, each [acctEnd] <> null)}),
    addNestedAccount = Table.TransformColumns(filterNestedAcctEnd, {"data", each Table.AddColumn(_, "Account", each Text.Combine({[acctStart], Text.From([acctEnd])}))}),
    expandNested = Table.ExpandTableColumn(addNestedAccount, "data", {"Transaction", "Account"}, {"Transaction", "Account"})
in
    expandNested

The above example transforms this:

...to this:

I suspect some hero will swoop in here in a couple of hours with a one-liner solution but, until then, this should sort you out.

Pete

[Jacb]

Thanks for the solution. 
Where you have stated the Text.Contains "Client" - my actual data doesn't contain the word Client - it contains the actual client name but I can't disclose that here.   What would I change this statement to use instead?
Thanks