Splitting a list

[flinch]

Hi, fairly new to all of this so I am sure there are easier ways to do what I am trying to do. Right now I will settle for any old way though, having tried for ages with no luck.

I have a debtors age analysis with 2 columns, Debtor name and balance. A long table with thousands of debtors, that are looked after by 6 credit controllers. Every so often, instead of a debtor name in the first column, it says Credit controller #3, and then all the debtors under that belong to credit controller #3, until  suddenly it says Credit controller #4, and then all below that belong to CC #4, and so on.

Ho do I get CC #x to appear in a column alongside the debtor name, or alternatively begin a new list every time there is a CC # entry instead of a debtor's name?

Any hep appreciated!

[Anonymous]

here another way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc67FcQgDETRXhQ7AX1dC8cR238Nq4EJLyA91pK9hzwypsr3LPkNaKpRs6XmlLbMg7KWRx7tjZeRde8mtmS9FO7eSgpbKoNzUAYLirlw1hVzbvyZnnqfXaGu3blboCrW7dSze98f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Colum1 = _t, Column1 = _t]),

    Headers = {"cc1","cc2","cc3","cc4"},
    grp = Table.Group(Source, {"Colum1"}, {{"all", each List.Skip(_[Colum1])}},GroupKind.Local,(x,y)=> Number.From(List.Contains(Headers,y[Colum1]))),
    tab=Table.FromColumns(grp[all],grp[Colum1])
in
    tab

[Anonymous]

alternatives

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY27EQAhCAV7ITaRr9biGHH913CgjJkr83bXAvcODWC3BV/PJxIXYRCxFFEQixZxkKgdcsenwFTYmEV5mMOKUjFMa0RvRDlSqS7lSJiKTjf+LmWXInIV/BR8uhal/QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Colum1 = _t, Column1 = _t]),

    Headers = {"cc1","cc2","cc3","cc4"},
    grp = Table.Group(Source, {"Colum1"}, {{"all", each Table.Skip(_)}},GroupKind.Local,(x,y)=> Number.From(List.Contains(Headers,y[Colum1]))),
    #"Expanded all" = Table.ExpandListColumn(grp, "all"),
    #"Expanded all1" = Table.ExpandRecordColumn(#"Expanded all", "all", {"Colum1", "Column1"}, {"debitor", "debit"})
in
    #"Expanded all1"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc67FcQgDETRXhQ7AX1dC8cR238Nq4EJLyA91pK9hzwypsr3LPkNaKpRs6XmlLbMg7KWRx7tjZeRde8mtmS9FO7eSgpbKoNzUAYLirlw1hVzbvyZnnqfXaGu3blboCrW7dSze98f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Colum1 = _t, Column1 = _t]),

    Headers = {"cc1","cc2","cc3","cc4"},
    grp = Table.Group(Source, {"Colum1"}, {{"all", each Table.Transpose(Table.Skip(_))}},GroupKind.Local,(x,y)=> Number.From(List.Contains(Headers,y[Colum1]))),
    #"Expanded all" = Table.Transpose(Table.ExpandTableColumn(grp, "all", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"})),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded all", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"cc1", type text}, {"cc1_1", Int64.Type}, {"cc2", type text}, {"cc2_2", Int64.Type}, {"cc3", type text}, {"cc3_3", Int64.Type}, {"cc4", type text}, {"cc4_4", Int64.Type}})
in
    #"Changed Type"

[flinch]

Blast, I was too clever for myself. In summarising how my data appeared, I simplified it too much, not realising what an impact it has on the code. I thought it would be easier to summarise my explanation and then adjust the code to my slightlier more comlicated situation, but it turns out I am unable to adjust the code properly! Aplogies for not being clear in the first instance.

There are additional columns showing the ageing of the balances, and so here is what the data actually looks like!

Hmm, unable to post an Excel file, so a s/s is below:

So "Group by Rep: (None)" is the first controller. "Group by Rep: 03" is the 2nd, and so on. All the credit controllers are:

Group by Rep: (None)

Group by Rep: 03

Group by Rep: 04

Group by Rep: 05

Group by Rep: 06

Group by Rep: 07

Thanks again.

[Anonymous]

Hey dude, 

Hopefully, this will work

Go to 

Add Column > Conditional Column 
" If "Customer" "Contains" Value "Group By " The Output "Customer" Else "null" (see below) 

After you've done that, just go to:

Transform > Fill > Fill down

Let me know if that works. 

If this post helps, then please consider Accept it as the solution to help the other members find it

[flinch]

Don't know what I was doing wrong - that worked in the end.

Many thanks for the help from everyone.

[flinch]

Thanks. Really simple when its put there! But for some reason I am doing that and getting nulls in every row, including the rows Group by Rep. This is the code that following those steps results in:

= Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1], "Group by") then [Column1] else null)

[Anonymous · Accepted solution]

It's all case sensitive. 

Your Group by text needs to be Group By - So try the following - 

= Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1], "Group By") then [Column1] else null)

[flinch]

Thanks for the solutions! let me try tonight and I will let you know how I fared.

To answer the first question, yes, the first row is Credit Controller #1.

[lbendlin]

is the very first row a debt collector (sorry, credit controller) name?

if yes then you can use this Power Query code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnZWMFTSUVKK1YlWcklNKskvAvONjE3AQikQISOgkImpGVgIqMMIpgMqbQzkm5qZIwuZAIXMzC2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Column3", each if [Column2] = "" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Column3"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Debtor"}, {"Column2", "Balance"}, {"Column3", "Credit Controller"}})
in
    #"Renamed Columns"