Forum Discussion
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 ag...
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)
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"
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.