Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 and go on for 36 periods like this. If the Period cell contains period 1-12 it should just follow like the last row in the resulting table below:
Anyone have any good ideas?
Solved! Go to Solution.
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"
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"
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 = (start, length) =>
List.Transform(
List.Numbers(start, length),
each if Number.Mod(_, 12) = 0 then 12 else Number.Mod(_, 12)
),
amnts = (step, length) => List.Numbers(step, length, 0),
descs = (desc, length) => List.Transform({1 .. length}, each desc & " month " & Text.From(_)),
years = (y, length) => List.Transform({1 .. length}, each y),
tac = Table.AddColumn(
mt,
"split",
each try
let
l = Number.From(Text.Split([period], "x"){1}),
s = Number.From(Text.Split([period], "x"){0})
in
Table.FromColumns(
{descs([desc], l), years([y], l), prds(s, l), amnts([amount] / l, l)},
Table.ColumnNames(mt)
)
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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
61 | |
54 | |
27 | |
16 | |
10 |