cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
flinch
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

 

 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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

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"

 

 

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:

Capture.JPG

 

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

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) 

 

Karlos_0-1595429028778.png


After you've done that, just go to:

 

Transform > Fill > Fill down

Karlos_1-1595429119350.png

 


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

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

 

Many thanks for the help from everyone.

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

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)

 

 

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

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"

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors