Forum Discussion
Anonymous
5 years agoNot applicable
Split Row based on year/month/length combination
Hi, I have this table in .xlsx: And in Power Query/Advanced editor i want to split up the rows, if it contains format of "5x36" meaning the row should start at period 5 in year 2020 an...
- Anonymous5 years ago
this is a little better
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi0uycxVSM4vLlEwVqhMTSwqVtJRMjIwMgBSphXGZkDK0AAMlGJ1gMorSooSIcpTUpOh6qCUoRFID0RpLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [desc = _t, y = _t, period = _t, amount = _t]), mt = Table.TransformColumnTypes(Origine,{{"desc", type text}, {"y", Int64.Type}, {"period", type text}, {"amount", Int64.Type}}), prds = (startd, length) => List.Transform({0..length-1}, each Date.ToText(Date.AddMonths(startd,_),"yyyy/MM")), descs = (desc, length) => List.Transform({1 .. length}, each desc & " month " & Text.From(_)), tac = Table.AddColumn( mt, "split", each try let l = Number.From(Text.Split([period], "x"){1}), s = Number.From(Text.Split([period], "x"){0}), sd=#date(mt[y]{0},s,1), tfc= Table.FromColumns({descs([desc], l), prds(sd, l), List.Repeat({[amount] / l }, l)},{"desc","date","amount"}) in Table.SplitColumn(tfc, "date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"y","period"}) otherwise Table.FromRecords({_}) ), #"Rimosse altre colonne" = Table.SelectColumns(tac,{"split"}), #"Tabella split espansa" = Table.ExpandTableColumn(#"Rimosse altre colonne", "split", {"desc", "y", "period", "amount"}, {"desc", "y", "period", "amount"}) in #"Tabella split espansa"
Anonymous
5 years agoNot applicable
this is a little better
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi0uycxVSM4vLlEwVqhMTSwqVtJRMjIwMgBSphXGZkDK0AAMlGJ1gMorSooSIcpTUpOh6qCUoRFID0RpLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [desc = _t, y = _t, period = _t, amount = _t]),
mt = Table.TransformColumnTypes(Origine,{{"desc", type text}, {"y", Int64.Type}, {"period", type text}, {"amount", Int64.Type}}),
prds = (startd, length) => List.Transform({0..length-1}, each Date.ToText(Date.AddMonths(startd,_),"yyyy/MM")),
descs = (desc, length) => List.Transform({1 .. length}, each desc & " month " & Text.From(_)),
tac = Table.AddColumn(
mt,
"split",
each
try
let
l = Number.From(Text.Split([period], "x"){1}),
s = Number.From(Text.Split([period], "x"){0}),
sd=#date(mt[y]{0},s,1),
tfc= Table.FromColumns({descs([desc], l), prds(sd, l), List.Repeat({[amount] / l }, l)},{"desc","date","amount"})
in Table.SplitColumn(tfc, "date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"y","period"})
otherwise Table.FromRecords({_})
),
#"Rimosse altre colonne" = Table.SelectColumns(tac,{"split"}),
#"Tabella split espansa" = Table.ExpandTableColumn(#"Rimosse altre colonne", "split", {"desc", "y", "period", "amount"}, {"desc", "y", "period", "amount"})
in
#"Tabella split espansa"