Forum Discussion
Splitting a list
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)
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
tabalternatives
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:
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.
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
Don't know what I was doing wrong - that worked in the end.
Many thanks for the help from everyone.
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.
