Forum Discussion

flinch's avatar
flinch
Helper I
6 years ago
Solved

Splitting a list

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!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

     

     

9 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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"
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        flinch
        Helper I
        6 years ago

        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.