Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 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? 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 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"

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

    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"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 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"